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?
source to share
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.
source to share