Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating