Wednesday, July 11, 2018

Finding all tables and columns used in a sp

http://www.sqlnotes.info/2015/11/19/get-all-referenced-tables-and-columns/#more-2586


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
)

No comments:

Post a Comment