mcp_sp_structure
Analyze SQL Server stored procedure structure to view parameters, dependencies, and source code for database development and maintenance.
Instructions
Analyze SQL Server stored procedure structure including parameters, dependencies, and source code
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sp_name | Yes | Fully qualified stored procedure name (schema.name), e.g. "eco.usp_Insert_EconomicMovement_v2" |
Implementation Reference
- Core handler function implementing mcp_sp_structure tool. Analyzes stored procedure by fetching info, parameters, dependencies, and source code using parallel SQL queries.export const mcp_sp_structure = async (args: { sp_name: string }): Promise<ToolResult<{ info: any; parameters: any[]; dependencies: any[]; definition: any }>> => { const { sp_name } = args; console.log('Executing mcp_sp_structure with:', { sp_name }); const qualified_name = normalizeSqlObjectName(sp_name); try { const pool = getPool(); // Enhanced query for basic information - matching Python implementation const infoQuery = ` SELECT OBJECT_SCHEMA_NAME(o.object_id) as schema_name, o.name as procedure_name, o.create_date, o.modify_date, m.definition as source_code, o.type_desc, CAST(ep.value AS NVARCHAR(MAX)) AS description FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id LEFT JOIN sys.extended_properties ep ON o.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description' WHERE o.type = 'P' AND o.object_id = OBJECT_ID(@qualified_name); `; // Enhanced query for parameters - matching Python implementation const paramsQuery = ` SELECT p.name as parameter_name, t.name as data_type, p.max_length, p.precision, p.scale, p.is_output, p.has_default_value, p.default_value, p.parameter_id FROM sys.parameters p INNER JOIN sys.types t ON p.system_type_id = t.system_type_id AND t.user_type_id = t.system_type_id WHERE p.object_id = OBJECT_ID(@qualified_name) ORDER BY p.parameter_id; `; // Enhanced query for dependencies - matching Python implementation const dependenciesQuery = ` SELECT DISTINCT dep.referenced_schema_name, dep.referenced_entity_name, o.type_desc as referenced_type, dep.referenced_database_name, dep.referenced_server_name FROM sys.dm_sql_referenced_entities(@qualified_name, 'OBJECT') dep LEFT JOIN sys.objects o ON OBJECT_ID(ISNULL(dep.referenced_database_name + '.', '') + ISNULL(dep.referenced_schema_name + '.', '') + dep.referenced_entity_name) = o.object_id WHERE dep.referenced_id IS NOT NULL ORDER BY dep.referenced_schema_name, dep.referenced_entity_name; `; // Execute all queries in parallel const [infoResult, paramsResult, dependenciesResult] = await Promise.all([ pool.request().input('qualified_name', qualified_name).query(infoQuery), pool.request().input('qualified_name', qualified_name).query(paramsQuery), pool.request().input('qualified_name', qualified_name).query(dependenciesQuery) ]); return { success: true, data: { info: infoResult.recordset[0] || null, parameters: paramsResult.recordset, dependencies: dependenciesResult.recordset, definition: infoResult.recordset[0]?.source_code || null } }; } catch (error: any) { console.error(`Error in mcp_sp_structure for SP ${sp_name}: ${error.message}`); return { success: false, error: error.message }; } };
- src/tools.ts:19-32 (registration)Tool registration entry in MCP_MSQL_TOOLS array, defining name, description, and JSON input schema for MCP protocol.{ name: "mcp_sp_structure", description: "Analyze SQL Server stored procedure structure including parameters, dependencies, and source code", inputSchema: { type: "object", properties: { sp_name: { type: "string", description: "Fully qualified stored procedure name (schema.name), e.g. \"eco.usp_Insert_EconomicMovement_v2\"" } }, required: ["sp_name"] } },
- TypeScript type definitions for input (args: { sp_name: string }) and output (ToolResult<{info, parameters, dependencies, definition}>) providing schema validation.export const mcp_sp_structure = async (args: { sp_name: string }): Promise<ToolResult<{ info: any; parameters: any[]; dependencies: any[]; definition: any }>> => {
- src/tools/index.ts:14-17 (registration)Re-export of the mcp_sp_structure handler from storedProcedureAnalysis module for barrel export.export { mcp_sp_structure, // Complete stored procedure analysis mcp_get_sp_all_info_simple // Para compatibilidad con server.ts } from './storedProcedureAnalysis.js';