Reputation: 6005
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.
Upvotes: 2
Views: 2896
Reputation: 1
this is what I use to get all tables used in a stored procedure but it does not return columns.
declare
@target_table as varchar(255) = N'my_sp',
@target_schema as varchar(255) = N'dbo';
select distinct od.name as depname
,1 as depnumber
,od.type as deptype
,sd.name as depschemaname
from sys.objects as o
inner join sys.schemas as s
on s.schema_id = o.schema_id
inner join sys.objects as od
on od.object_id = o.parent_object_id
inner join sys.schemas as sd
on sd.schema_id = od.schema_id
where s.name = @target_schema
and o.name = @target_table and od.type = 'U'
union
select distinct od.name as depname
,case
when od.type = 'P'
then np.procedure_number
else 1
end as depnumber
,od.type as deptype
,sd.name as depschemaname
from sys.sql_expression_dependencies d
inner join sys.objects o
on o.object_id = d.referencing_id
inner join sys.schemas s
on s.schema_id = o.schema_id
inner join sys.objects od
on od.object_id = d.referenced_id
inner join sys.schemas sd
on sd.schema_id = od.schema_id
left join sys.numbered_procedures np
on np.object_id = d.referenced_id
where s.name = @target_schema
and o.name = @target_table and od.type = 'U';
Upvotes: 0
Reputation: 7314
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.
Upvotes: 5