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.
