如何通过Pymssql查询数据库可用存储过程及参数？

Awesome question—this is exactly the kind of problem that SQL Server's built-in system catalogs are designed to solve! Since you're already using pymssql and pandas to run stored procedures, you can leverage these system views to automatically fetch all user-defined stored procedures (and their parameters) across your databases, no more relying on incomplete manual records.

Let me break this down into actionable steps, tailored to your workflow:

1. Fetch All User-Defined Stored Procedures #

First, you can query sys.procedures (along with sys.schemas to get schema context) to get a list of all non-system stored procedures in a database. This gives you names, creation/modification dates, and schema info—super useful if you have procs spread across multiple schemas.

Here's the SQL query wrapped in a Python function that matches your existing pymssql setup:

import pymssql
import pandas as pd

def get_all_stored_procs(server, port, user, password, database):
    # Establish connection (same as your existing workflow)
    conn = pymssql.connect(
        host=server,
        port=port,
        user=user,
        password=password,
        database=database
    )
    
    # Query to pull user-defined stored procedures
    proc_query = """
    SELECT
        SCHEMA_NAME(p.schema_id) AS schema_name,
        p.name AS procedure_name,
        p.create_date AS created_on,
        p.modify_date AS last_modified_on
    FROM sys.procedures p
    WHERE p.is_ms_shipped = 0  -- Exclude system-owned procedures
    ORDER BY schema_name, procedure_name;
    """
    
    # Load results into a pandas DataFrame for easy handling
    procs_df = pd.read_sql_query(proc_query, conn)
    
    conn.close()
    return procs_df

# Example usage (match your connection details)
my_procs = get_all_stored_procs(
    server='ip.ad.dr.es.',
    port='1433',
    user='db_user',
    password='pdws',
    database='db_name'
)

print(my_procs)

2. Fetch Parameters for Specific (or All) Stored Procedures #

To get parameter details—names, data types, nullability, default values, etc.—join sys.procedures with sys.parameters and sys.types (to get readable data type names). This gives you everything you need to construct EXEC calls dynamically.

Here's a reusable function for this; you can get parameters for all procs, or filter to a specific one safely:

def get_proc_parameters(server, port, user, password, database, proc_name=None, schema='dbo'):
    conn = pymssql.connect(
        host=server,
        port=port,
        user=user,
        password=password,
        database=database
    )
    
    base_query = """
    SELECT
        SCHEMA_NAME(p.schema_id) AS schema_name,
        p.name AS procedure_name,
        prm.parameter_id AS param_position,
        prm.name AS parameter_name,
        t.name AS data_type,
        prm.max_length AS max_length,
        prm.is_nullable AS is_optional,
        prm.default_value AS default_value,
        prm.is_output AS is_output_param
    FROM sys.procedures p
    JOIN sys.parameters prm ON p.object_id = prm.object_id
    JOIN sys.types t ON prm.system_type_id = t.system_type_id 
        AND prm.user_type_id = t.user_type_id
    WHERE p.is_ms_shipped = 0
    """
    
    # Add filters for a specific proc (uses parameterized queries to avoid SQL injection)
    if proc_name:
        base_query += " AND p.name = %s AND SCHEMA_NAME(p.schema_id) = %s"
        params_df = pd.read_sql_query(base_query, conn, params=(proc_name, schema))
    else:
        params_df = pd.read_sql_query(base_query, conn)
    
    conn.close()
    return params_df

# Get parameters for all procs
all_params = get_proc_parameters(
    server='ip.ad.dr.es.',
    port='1433',
    user='db_user',
    password='pdws',
    database='db_name'
)

# Or get parameters for a single proc
specific_params = get_proc_parameters(
    server='ip.ad.dr.es.',
    port='1433',
    user='db_user',
    password='pdws',
    database='db_name',
    proc_name='stored_proc_name',
    schema='dbo'
)

print(specific_params)

3. Handling Multiple Databases #

Since your procs are spread across 2-3 databases, loop through each database name and run the above functions for each one—just update the database parameter in the connection string for each iteration.

Bonus Tips #

• Avoid SQL Injection: The second function uses parameterized queries when filtering by proc name—always do this instead of string concatenation if you're accepting user input.
• Output Parameters: The is_output_param column flags parameters meant for output; you'll need to handle these differently in pymssql if you need to retrieve their values.
• Auto-Generate Your Module: Use the results from these queries to auto-generate hardcoded proc calls in your Python module—saving you even more manual work!

This approach eliminates the need for manual record-keeping and ensures you always have an up-to-date list of available procs and their requirements.

内容的提问来源于stack exchange，提问作者HDSci