SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get All Referenced Tables and Columns

I was always challenged when my customers asked me what tables and columns are referenced by a stored procedure which was written many years ago by the guy who left the company 5 years ago. When I Google the solution, I was always told that sys.sql_dependencies and sys.sql_expression_dependencies can tell. At the end of the day, I figured that depending on the complexity of the procedure, those 2 views couldn’t always give me accurate information as needed, dynamic SQLs for instance. Even worse, my customer also asked me if a table was accessed by anyone and what columns were referenced. I realized that I have to write something to get it done.

One thing jumped into my mind was to parse the query plans. I created a function,

create function GetReferencedColumns(@Plan xml)
returns table
	with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p),
	src as(
					c.value('@Server', 'varchar(128)') ServerName, 
					c.value('@Database', 'varchar(128)') DatabaseName, 
					c.value('@Schema', 'varchar(128)') SchemaName, 
					c.value('@Table', 'varchar(128)') TableName, 
					c.value('@Column', 'varchar(128)') ColumnName
			from @plan.nodes('//p:ColumnReference') n(c)
	select distinct ServerName, DatabaseName, SchemaName, TableName, ColumnName
	from src
	where TableName is not null

wrote a SQL,

select * 
from test0.tempdb.sys.all_objects a
	inner join master.sys.all_objects b on a.object_id = b.object_id

got Estimated Execution Plan, and passed the plan xml to this function. What I got was

ServerName           DatabaseName              SchemaName TableName            ColumnName
-------------------- ------------------------- ---------- -------------------- --------------------
NULL                 [master]                  [sys]      [sysschobjs]         created
NULL                 [master]                  [sys]      [sysschobjs]         id
NULL                 [master]                  [sys]      [sysschobjs]         modified
NULL                 [master]                  [sys]      [sysschobjs]         name
NULL                 [master]                  [sys]      [sysschobjs]         nsclass
NULL                 [master]                  [sys]      [sysschobjs]         nsid
NULL                 [master]                  [sys]      [sysschobjs]         pid
NULL                 [master]                  [sys]      [sysschobjs]         status
NULL                 [master]                  [sys]      [sysschobjs]         type
NULL                 [master]                  [sys]      [syssingleobjrefs]   class
NULL                 [master]                  [sys]      [syssingleobjrefs]   depid
NULL                 [master]                  [sys]      [syssingleobjrefs]   depsubid
NULL                 [master]                  [sys]      [syssingleobjrefs]   indepid
NULL                 [mssqlsystemresource]     [sys]      [syspalnames]        class
NULL                 [mssqlsystemresource]     [sys]      [syspalnames]        name
NULL                 [mssqlsystemresource]     [sys]      [syspalnames]        value
[test0]              [tempdb]                  [sys]      [all_objects]        create_date
[test0]              [tempdb]                  [sys]      [all_objects]        is_ms_shipped
[test0]              [tempdb]                  [sys]      [all_objects]        is_published
[test0]              [tempdb]                  [sys]      [all_objects]        is_schema_published
[test0]              [tempdb]                  [sys]      [all_objects]        modify_date
[test0]              [tempdb]                  [sys]      [all_objects]        name
[test0]              [tempdb]                  [sys]      [all_objects]        object_id
[test0]              [tempdb]                  [sys]      [all_objects]        parent_object_id
[test0]              [tempdb]                  [sys]      [all_objects]        principal_id
[test0]              [tempdb]                  [sys]      [all_objects]        schema_id
[test0]              [tempdb]                  [sys]      [all_objects]        type
[test0]              [tempdb]                  [sys]      [all_objects]        type_desc

(28 row(s) affected)

It worked.
I wrote a little C# program to get all the estimated execution plan from all the procedures, functions, and views; got the plans from the cache; and also fired up the SQL Profiler to capture queries and plans for days. With this function, I finally created a big map of object dependencies.
Hope this can be helpful to you as well.

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.


Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...