CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2012
Trisped
Trisped

Reputation: 6005

How can I get a list of all columns referenced in a stored procedure?

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

Answers (2)

Haran
Haran

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

Stephen Turner
Stephen Turner

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

Related Questions