list_stored_procs
Discover and filter stored procedures in SQL Server databases by schema or name pattern to analyze database structure and manage procedures effectively.
Instructions
List available stored procedures in the database.
Args:
schema: Filter by schema name (e.g., 'dbo')
pattern: Filter by name pattern using SQL LIKE syntax (e.g., 'sp_%', '%User%')
Returns:
Dictionary with:
- procedures: List of procedure info (schema, name, created, modified)
- count: Number of procedures foundInput Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | ||
| pattern | No |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||
Implementation Reference
- The core handler function for the 'list_stored_procs' tool, decorated with @mcp.tool() which serves as its registration. It queries the INFORMATION_SCHEMA.ROUTINES view to list stored procedures, optionally filtered by schema and LIKE pattern, and formats the results into a structured dictionary.
@mcp.tool() def list_stored_procs( schema: str | None = None, pattern: str | None = None, ) -> dict[str, Any]: """List available stored procedures in the database. Args: schema: Filter by schema name (e.g., 'dbo') pattern: Filter by name pattern using SQL LIKE syntax (e.g., 'sp_%', '%User%') Returns: Dictionary with: - procedures: List of procedure info (schema, name, created, modified) - count: Number of procedures found """ try: manager = get_connection_manager() query = """ SELECT ROUTINE_SCHEMA as [schema], ROUTINE_NAME as [name], CREATED as [created], LAST_ALTERED as [modified] FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_CATALOG = DB_NAME() """ params: list[Any] = [] if schema: query += " AND ROUTINE_SCHEMA = %s" params.append(schema) if pattern: query += " AND ROUTINE_NAME LIKE %s" params.append(pattern) query += " ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME" rows = manager.execute_query(query, tuple(params) if params else None) procedures = [ { "schema": row["schema"], "name": row["name"], "created": row["created"].isoformat() if row["created"] else None, "modified": row["modified"].isoformat() if row["modified"] else None, } for row in rows ] return { "procedures": procedures, "count": len(procedures), } except Exception as e: logger.error(f"Error listing stored procedures: {e}") return {"error": str(e)}