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

Trisped

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.

Stephen Turner

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How can I capture the results of this Stored Procedure in a List<>?

From Dev

How can I get output parameter to stored procedure in POCO?

From Dev

how can I get cursor data with calling stored procedure in npgsql

From Dev

How to get total number of columns in stored procedure?

From Dev

How can I get a list of all functions stored in the database for all the schemas in db2uw?

From Dev

Can I get all assemblies referenced by a website

From Dev

Can I get all assemblies referenced by a website

From Dev

How can I debug a Stored Procedure?

From Dev

How I can transform a stored procedure to a view?

From Dev

How can I merge 3 distinct Sqls by the same grouping but different columns (Sql Server Stored Procedure)

From Dev

SQL how to get specific columns from a stored procedure inside a stored procedure

From Dev

How can I use SqlDataReader to get grouped data returned by a stored procedure?

From Dev

How can I select all items referenced by a specific list:reference field?

From Dev

How can I list all nix store paths referenced in a build generation?

From Dev

I can't get records from a stored procedure

From Dev

Can I get the ID/name of database that a stored procedure is called from?

From Dev

How to get all SSRS Subscription Data using stored procedure?

From Dev

MSSQL stored procedure select all columns

From Dev

How can I specify a Stored Procedure to be used in the SSRS Report Wizard?

From Dev

How can I make a Stored procedure return a bit value?

From Dev

how can i check return value of stored procedure in entity framework

From Dev

How can I ignore the resultset of a stored procedure in a trigger?

From Dev

How can I always return 3 values in this stored procedure?

From Dev

How can I set a lock inside a stored procedure?

From Dev

How can I serialize multiple executions of a stored procedure with the same arguments?

From Dev

How can I insert result from stored procedure to a new table?

From Dev

How can i use oracle object type in stored procedure?

From Dev

How can i set object type to out in oracle stored procedure?

From Dev

How I can debug a stored procedure with a VARRAY parameter?

Related Related

  1. 1

    How can I capture the results of this Stored Procedure in a List<>?

  2. 2

    How can I get output parameter to stored procedure in POCO?

  3. 3

    how can I get cursor data with calling stored procedure in npgsql

  4. 4

    How to get total number of columns in stored procedure?

  5. 5

    How can I get a list of all functions stored in the database for all the schemas in db2uw?

  6. 6

    Can I get all assemblies referenced by a website

  7. 7

    Can I get all assemblies referenced by a website

  8. 8

    How can I debug a Stored Procedure?

  9. 9

    How I can transform a stored procedure to a view?

  10. 10

    How can I merge 3 distinct Sqls by the same grouping but different columns (Sql Server Stored Procedure)

  11. 11

    SQL how to get specific columns from a stored procedure inside a stored procedure

  12. 12

    How can I use SqlDataReader to get grouped data returned by a stored procedure?

  13. 13

    How can I select all items referenced by a specific list:reference field?

  14. 14

    How can I list all nix store paths referenced in a build generation?

  15. 15

    I can't get records from a stored procedure

  16. 16

    Can I get the ID/name of database that a stored procedure is called from?

  17. 17

    How to get all SSRS Subscription Data using stored procedure?

  18. 18

    MSSQL stored procedure select all columns

  19. 19

    How can I specify a Stored Procedure to be used in the SSRS Report Wizard?

  20. 20

    How can I make a Stored procedure return a bit value?

  21. 21

    how can i check return value of stored procedure in entity framework

  22. 22

    How can I ignore the resultset of a stored procedure in a trigger?

  23. 23

    How can I always return 3 values in this stored procedure?

  24. 24

    How can I set a lock inside a stored procedure?

  25. 25

    How can I serialize multiple executions of a stored procedure with the same arguments?

  26. 26

    How can I insert result from stored procedure to a new table?

  27. 27

    How can i use oracle object type in stored procedure?

  28. 28

    How can i set object type to out in oracle stored procedure?

  29. 29

    How I can debug a stored procedure with a VARRAY parameter?

HotTag

Archive