list_stored_procs
Discover and filter stored procedures in SQL Server databases by schema or name pattern to analyze database structure and manage procedures.
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 found
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | ||
| pattern | No |
Implementation Reference
- The handler function for the 'list_stored_procs' tool. It is decorated with @mcp.tool(), serving as both implementation and registration. Queries INFORMATION_SCHEMA.ROUTINES for stored procedures, filters by schema and pattern if provided, and returns a list of procedures with metadata.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)}