SQL Server script to summarize stored procedure

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

  • Tables selected with column names
  • Tables modified (inserted, updated, or deleted) with column names (if update)
  • Any child stored procedure called with the same total as above two points.
  • Parameters passed

Does anyone know of a T-SQL script like this to summarize a stored procedure?

+3


source to share


2 answers


You can use sp_help

to get information about parameters and information about an object.

EXEC sp_help yourProcedure;

      

Then to get what it uses for tables and columns, you can use sp_depends

EXEC sp_depends yourProcedure;

      

Here is a helpful link that lists the sql object dependencies

Or you can also use. But this 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 it is sp_depends

deprecated.

If the store procedure uses dynamic sql. Then you should only use:

sp_helptext yourProcedure 

      

This will return the contents of the procedure.

If you have dynamic sql in storage procedure. You can 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 storage procedure.

+3


source


This will give you the actual text of the stored procedure, which you can copy and paste into another query window and parse.



sp_helptext yourProcedure

      

0


source







All Articles