I have a number of stored procedures which use CTEs, temp tables, table variables, and sub queries and I need to get the list of all columns (including database, schema, and table/view) used in the stored procedure. I do not need to get the columns in the temp tables, table variables, or CTEs. I just need the referenced columns which are defined in a table or view in a database on my server.
I tried sys.dm_sql_referenced_entities
and sys.sql_expression_dependencies
but they do not return columns after the first select query or selected in a CTE.
When a stored procedure is executed it is parsed and compiled into a query plan, this is cached and you can access it via sys.dm_exec_cached_plans and sys.dm_exec_query_plan in XML format. The query plan records the 'output list' of each section of the parsed code. Seeing which columns are used by the stored procedure is just a matter of querying this XML, like this:
--Execute the stored procedure to put its query plan in the cache
exec sys.sp_columns ''
DECLARE @TargetObject nvarchar(100) = 'sys.sp_columns';
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
WHERE objtype = 'Proc'
), 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
[Database],
[Schema],
[Table],
[Column]
FROM ColumnReferences
WHERE
[Database] IS NOT NULL AND
ob = OBJECT_ID(@TargetObject, 'P')
Caveat emptor this depends on how you define 'used'. It may be that a CTE within your stored procedure references 5 columns from a table, but then when this CTE is used only three of the columns are passed on. The query optimizer may ignore these extra fields and not include them in the plan. On the flip side the optimizer may decide that it can make a more efficient query by including extra fields in an output to enable it to use a better index later on. This code will return the columns used by the query plan, they may not exactly be the columns that are in the stored procedure code.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments