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
as
return(
	with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p),
	src as(
			select 
					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.

Leave a Comment

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.