SQL Server script to summarize a stored procedure

Question

For a given stored procedure, I want the following summary -

• Tables selected with column names
• Tables modified (insert, update or delete) with column names (if update)
• Any child stored procedure being called with same summary as above two points.
• Parameters passed

Does anyone know such a T-SQL script to summarize a stored procedure?

Answer 1

You can use sp_help to get information about with parameters and information about the object.

EXEC sp_help yourProcedure;

Then to get what it is using for tables and columns you can use sp_depends

EXEC sp_depends yourProcedure;

Here is a useful link that list the sql Object Dependencies

Or you can also use. But it is only for 2008 and above.

SELECT * FROM sys.dm_sql_referenced_entities ('dbo.yourProcedure', 'OBJECT');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.yourProcedure', 'OBJECT');

because sp_depends is deprecated.

If the store procedure are using dynamic sql. Then the only you have to use:

sp_helptext yourProcedure 

This will return the content of the procedure.

If you have Dynamic sql in you store procedure. You could do something like this:

DECLARE @tbl TABLE(Text VARCHAR(MAX))
INSERT INTO @tbl
EXEC sp_helptext yourProcedure

SELECT 
    sysTables.name 
FROM 
    dbo.sysobjects AS sysTables
WHERE EXISTS
    (
        SELECT
            NULL
        FROM
            @tbl AS tbl
        WHERE
            tbl.Text LIKE '%'+sysTables.name+'%'
    )

This will return the objects used in the store procedure.