Search code examples
sql-servert-sqlprocedureintrospection

Is there any way to introspect a TSQL procedure for tables and columns accessed by it?

I find myself needing to document all tables and columns that are accessed by a large number of stored procedures in SQL Server. I can search for stored procedures that have this or that text in the body but that isn't what's needed.

Solution

  • There are numerous ways to find stored procedure dependencies. A cursory google search will land you:

    https://msdn.microsoft.com/en-us/library/ms345404.aspx

    Using SQL Server Management Studio

    To view the dependencies of a procedure in Object Explorer

    In Object Explorer, connect to an instance of Database Engine and then expand that instance.

    Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

    Expand Stored Procedures, right-click the procedure and then click View Dependencies.

    View the list of objects that depend on the procedure.

    View the list of objects on which the procedure depends.

    Click OK.

    or using SQL:

    SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('table name', 'OBJECT');

    etc

    To do all stored procedures you could write a join on sys.object to plug into the DMV listed above, with some potential tweaking... but it's definitly doable. Think there's probably some SSMS addons to do it as well. Red Gate maybe?