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?
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.
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