WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns1) ,CompiledPlan AS ( SELECT (SELECT query_plan FROM sys.dm_exec_query_plan(cp.plan_handle)) qp, (SELECT ObjectID FROM sys.dm_exec_sql_text(cp.plan_handle)) ob FROM sys.dm_exec_cached_plans cp ) ,ColumnReferences AS ( SELECT DISTINCT ob ,p.query('.').value('./ns1:ColumnReference[1]/@Database', 'sysname') AS [Database] ,p.query('.').value('./ns1:ColumnReference[1]/@Schema', 'sysname') AS [Schema] ,p.query('.').value('./ns1:ColumnReference[1]/@Table', 'sysname') AS [Table] ,p.query('.').value('./ns1:ColumnReference[1]/@Column', 'sysname') AS [Column] FROM CompiledPlan CROSS APPLY qp.nodes('//ns1:ColumnReference') t(p) ) SELECT REPLACE(REPLACE([Table],'[',''),']','') AS [table] ,[Column] AS [column] FROM ColumnReferences WHERE [Database] = '[SL]' AND [Schema] = '[dbo]'

https://stackoverflow.com/questions/29023514/how-can-i-get-a-list-of-all-columns-referenced-in-a-stored-procedure

before executing your queries or store procedures you should clean your cache DBCC FREEPROCCACHE 17.08.18

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

• Home
• Notebook
• Contact us

• Privacy Statement
• 

Copyleft ☠ 2024