Tags

, ,


The following procedure will retrieve all the user-created stored procedures: 
 

CREATE PROCEDURE dbo.listProcedures 
AS 
BEGIN 
    SET NOCOUNT ON 
    SELECT 
        ROUTINE_NAME 
    FROM 
        INFORMATION_SCHEMA.ROUTINES 
    WHERE 
        ROUTINE_TYPE = ‘PROCEDURE’ 
        AND OBJECTPROPERTY 
        ( 
            OBJECT_ID(ROUTINE_NAME), 
            ‘IsMsShipped’ 
        ) = 0 
    ORDER BY 
        ROUTINE_NAME 
END 
GO

 
(We use the ObjectProperty function to filter out procedures that are built into the database automatically, such as the SourceSafe-related dt_* procedures.) 
 
So from ASP: 
 

<% 
    set conn = CreateObject(“ADODB.Connection”) 
    conn.open “<connection string>” 
    set rs = conn.execute(“EXEC dbo.listProcedures”) 
    do while not rs.eof 
        response.write rs(0) & “<br>” 
        rs.movenext 
    loop 
    rs.close: set rs = nothing 
    conn.close: set conn = nothing 
%>

 
Showing all procedures *and* their parameters 
 
Borrowing a bit of code from Article #2463, here is a code snippet that will show *all* stored procedures, and *all* of their parameters. 
 

SELECT  
    [Procedure Name] = ir.ROUTINE_NAME, 
    [Parameter Name] = COALESCE(ip.PARAMETER_NAME, ‘<no params>’), 
    [Data Type] = COALESCE(UPPER(ip.DATA_TYPE) + CASE 
        WHEN ip.DATA_TYPE IN (‘NUMERIC’, ‘DECIMAL’) THEN  
            ‘(‘ + CAST(ip.NUMERIC_PRECISION AS VARCHAR)  
            + ‘, ‘ + CAST(ip.NUMERIC_SCALE AS VARCHAR) + ‘)’  
        WHEN RIGHT(ip.DATA_TYPE, 4) = ‘CHAR’ THEN 
            ‘(‘ + CAST(ip.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ‘)’ 
        ELSE ” END + CASE ip.PARAMETER_MODE  
        WHEN ‘INOUT’ THEN ‘ OUTPUT’ ELSE ‘ ‘ END, ‘-‘) 
FROM  
    INFORMATION_SCHEMA.ROUTINES ir 
    LEFT OUTER JOIN 
    INFORMATION_SCHEMA.PARAMETERS ip 
    ON ir.ROUTINE_NAME = ip.SPECIFIC_NAME 
WHERE 
    ir.ROUTINE_TYPE = ‘PROCEDURE’ 
    AND COALESCE(OBJECTPROPERTY 
    ( 
        OBJECT_ID(ip.SPECIFIC_NAME), 
        ‘IsMsShipped’ 
    ), 0) = 0 
ORDER BY  
    ir.ROUTINE_NAME, 
    ip.ORDINAL_POSITION
Advertisements