none
List tables and column names a stored procedure is querying RRS feed

  • Question

  • Hello,

    Is there a way to list tables and column names that a stored procedure is querying, i.e, selecting from, using T-SQL?  If yes, is there a way to also show data types for the columns that a stored procedure is querying, i.e, selecting from?

    Thank you,  

    Lenfinkel

    Monday, March 2, 2015 4:49 PM

Answers

  • Hello Prashanth,

    I worked it out... I added to the query below to show functions as well. This is exactly what I was looking for.  See below.

    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql += N'UNION ALL
    SELECT
      [database]  = ''' + REPLACE(name, '''', '''''') + ''',
      [stored procedure/function] = QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name)
                    COLLATE Latin1_General_CI_AI,
      [table]     = QUOTENAME(d.referenced_schema_name) + ''.''
                  + QUOTENAME(d.referenced_entity_name)
                    COLLATE Latin1_General_CI_AI,
      [column]    = QUOTENAME(d.referenced_minor_name)
                    COLLATE Latin1_General_CI_AI
    FROM ' + QUOTENAME(name) + '.sys.schemas AS s
    INNER JOIN ' + QUOTENAME(name) + '.sys.all_objects AS o
    ON s.[schema_id] = o.[schema_id]
    CROSS APPLY ' + QUOTENAME(name)
    + '.sys.dm_sql_referenced_entities'
    + '(QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name), N''OBJECT'') AS d
    WHERE d.referenced_minor_id > 0
    AND o.[Type] IN (''P'',''TF'',''FN'') and o.[Is_MS_Shipped] = 0
    order by [database], [stored procedure/function], [table]'
    FROM sys.databases
      WHERE 
      database_id > 4
      AND [state] = 0

    SET @sql = STUFF(@sql,1,11,'');

    EXEC sp_executesql @sql;


    Lenfinkel

    Tuesday, March 3, 2015 3:35 PM

All replies

  • You can make use of sp_depends for that

    https://msdn.microsoft.com/en-us/library/ms189487.aspx?f=255&MSPPError=-2147217396

    If you want to track dynamic sql references also use sys.sql_modules view

    http://visakhm.blogspot.in/2012/03/advantages-of-using-syssqlmodules-view.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, March 2, 2015 4:53 PM
  • Try this

    SELECT DISTINCT p.name AS proc_name, t.name AS table_name
    FROM sys.sql_dependencies d 
    INNER JOIN sys.procedures p ON p.object_id = d.object_id
    INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
    ORDER BY proc_name, table_name
    --Prashanth

    Monday, March 2, 2015 4:57 PM
  • Thank you, Visakh.  I don't see it working for stored procedures.  How can get columns that a stored procedure is selecting from?  Could you please provide an actual example?

    - Lenfinkel


    Lenfinkel

    Monday, March 2, 2015 5:04 PM
  • Thank you, Prashanth.

    Your query does not show column names that a stored procedure is selecting from (in its query)?  Do you have a query or a way to do it?  If yes, could you please provide an example?


    Lenfinkel

    Monday, March 2, 2015 5:06 PM
  • Try this

    Reference

    http://www.sqlusa.com/bestpractices2008/object-dependency/

    SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
           ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.'
                              + ed.referenced_entity_name,
           ColumnName = c.name,
           ReferencedObjectType = o2.type,
           ReferencingObjecType = o1.type
    FROM   sys.sql_expression_dependencies ed
           INNER JOIN sys.objects o1
                   ON ed.referencing_id = o1.object_id
           INNER JOIN sys.objects o2
                   ON ed.referenced_id = o2.object_id
           INNER JOIN sys.sql_dependencies d
                   ON ed.referencing_id = d.object_id
                      AND d.referenced_major_id = ed.referenced_id
           INNER JOIN sys.columns c
                   ON c.object_id = ed.referenced_id
                      AND d.referenced_minor_id = c.column_id
    --Prashanth

    Monday, March 2, 2015 5:13 PM
  • Thank you, Prashanth.

    Your query does not show column names that a stored procedure is selecting from (in its query)?  Do you have a query or a way to do it?  If yes, could you please provide an example?


    Lenfinkel

    do you mean even the columns that stored procedures uses for filter etc in its query and not in actual resultset?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, March 2, 2015 5:37 PM
  • Visakh,

    Here is an an example below... I would like to get a result back of tables and column names that the stored proc below queries, i.e, selecting from - regardless of any dependcies.  

    ALTER PROC [dbo][sp_Impaired_Load] AS


    SELECT      PROD_DT,
                     ACCT_NBR,
                    NOTE_NBR,
                    ACCT_SHORT_NM,
                    ORIG_MAT_DT, 
    FROM   LOAN_ACCT_TABLE


    Lenfinkel

    Monday, March 2, 2015 5:44 PM
  • Prashanth,

    Here is an an example below... I would like to get a result back of tables and column names that the stored proc below queries, i.e, selecting from - regardless of any dependencies. 

    ALTER PROC [dbo][sp_Impaired_Load] AS


    SELECT      PROD_DT,
                     ACCT_NBR,
                    NOTE_NBR,
                    ACCT_SHORT_NM,
                    ORIG_MAT_DT,
    FROM   LOAN_ACCT_TABLE


    Lenfinkel

    Monday, March 2, 2015 5:45 PM
  • Try this

    CREATE PROC [dbo].[sp_Impaired_Load] AS
    SELECT      PROD_DT,
                     ACCT_NBR, 
                    NOTE_NBR, 
                    ACCT_SHORT_NM, 
                    ORIG_MAT_DT
    FROM   LOAN_ACCT_TABLE
    Then execute the below SQL

    SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
           ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.'
                              + ed.referenced_entity_name,
           ColumnName = c.name,
           ReferencedObjectType = o2.type,
           ReferencingObjecType = o1.type
    FROM   sys.sql_expression_dependencies ed
           INNER JOIN sys.objects o1
                   ON ed.referencing_id = o1.object_id
           INNER JOIN sys.objects o2
                   ON ed.referenced_id = o2.object_id
           INNER JOIN sys.sql_dependencies d
                   ON ed.referencing_id = d.object_id
                      AND d.referenced_major_id = ed.referenced_id
           INNER JOIN sys.columns c
                   ON c.object_id = ed.referenced_id
                      AND d.referenced_minor_id = c.column_id
    WHERE  SCHEMA_NAME(o1.schema_id) + '.' + o1.name = 'dbo.sp_Impaired_Load'
    ORDER  BY ReferencedObject,
              c.column_id; 
    --Prashanth

    Monday, March 2, 2015 5:59 PM
  • Thank you, Prashanth.

    That did not work.

    The closest I have gotten is with this code below.  How can I run this for all stored procs in a database?  As you see, it requires a parameter.  How can I run it without passing it a name of a specific stored proc?

    SELECT referenced_server_name AS server
        , referenced_database_name AS database_name
        , referenced_schema_name AS schema_name
        , referenced_entity_name AS referenced_entity
        , referenced_minor_name AS column_name
    FROM sys.dm_sql_referenced_entities ('dbo.sp_Impaired_Load, 'OBJECT');
    GO


    Lenfinkel

    Monday, March 2, 2015 6:12 PM
  • You will have to insert temp tables by querying all stored procedures select * from DatabaseName.information_schema.routines where routine_type = 'PROCEDURE' then loop through them and get a final output probably with another temp table.
    Monday, March 2, 2015 6:44 PM
  • try this

    Remove the columns as per your requirement

    SELECT
    sre.referenced_server_name AS server
        , sre.referenced_database_name AS database_name
        , sre.referenced_schema_name AS schema_name
        , sre.referenced_entity_name AS referenced_entity
        , sre.referenced_minor_name AS column_name
      ,SourceSchema  = OBJECT_SCHEMA_NAME(sed.referencing_id)
     ,SourceObject  = OBJECT_NAME(sed.referencing_id)
     ,ReferencedDB  = ISNULL(sre.referenced_database_name, DB_NAME())
     ,ReferencedSchema = ISNULL(sre.referenced_schema_name,
    OBJECT_SCHEMA_NAME(sed.referencing_id))
     ,ReferencedObject             = sre.referenced_entity_name
     ,ReferencedColumnID   = sre.referenced_minor_id
     ,ReferencedColumn             = sre.referenced_minor_name
    FROM sys.sql_expression_dependencies sed
    CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
    + '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
    --Prashanth

    Monday, March 2, 2015 7:28 PM
  • Thank you, Prashanth.  The link I had sent you earlier works the best for me -- the code below is from the link. This only works to get all stored procs with columns that it queries. How can I also add the Functions (udfts) to this query, i.e., to get the coumns that functions queries? 

    -- List all columns referenced in all procedures of all databases

    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql += N'UNION ALL
    SELECT
      [database]  = ''' + REPLACE(name, '''', '''''') + ''',
      [procedure] = QUOTENAME(s.name) + ''.'' + QUOTENAME(p.name)
                    COLLATE Latin1_General_CI_AI,
      [table]     = QUOTENAME(referenced_schema_name) + ''.''
                  + QUOTENAME(referenced_entity_name)
                    COLLATE Latin1_General_CI_AI,
      [column]    = QUOTENAME(referenced_minor_name)
                    COLLATE Latin1_General_CI_AI
    FROM ' + QUOTENAME(name) + '.sys.schemas AS s
    INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS p
    ON s.[schema_id] = p.[schema_id]
    CROSS APPLY ' + QUOTENAME(name)
    + '.sys.dm_sql_referenced_entities'
    + '(QUOTENAME(s.name) + ''.'' + QUOTENAME(p.name), N''OBJECT'') AS d
    WHERE d.referenced_minor_id > 0'
    FROM sys.databases
      WHERE
      name = 'DM_TRAN_INQ_MAINT'   --<== comment out name to get result for all databases
      AND database_id > 4
      AND [state] = 0;

    SET @sql = STUFF(@sql,1,11,'');

    EXEC sp_executesql @sql;

      


    Lenfinkel

    Monday, March 2, 2015 7:40 PM
  • Hello Prashanth,

    I worked it out... I added to the query below to show functions as well. This is exactly what I was looking for.  See below.

    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql += N'UNION ALL
    SELECT
      [database]  = ''' + REPLACE(name, '''', '''''') + ''',
      [stored procedure/function] = QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name)
                    COLLATE Latin1_General_CI_AI,
      [table]     = QUOTENAME(d.referenced_schema_name) + ''.''
                  + QUOTENAME(d.referenced_entity_name)
                    COLLATE Latin1_General_CI_AI,
      [column]    = QUOTENAME(d.referenced_minor_name)
                    COLLATE Latin1_General_CI_AI
    FROM ' + QUOTENAME(name) + '.sys.schemas AS s
    INNER JOIN ' + QUOTENAME(name) + '.sys.all_objects AS o
    ON s.[schema_id] = o.[schema_id]
    CROSS APPLY ' + QUOTENAME(name)
    + '.sys.dm_sql_referenced_entities'
    + '(QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name), N''OBJECT'') AS d
    WHERE d.referenced_minor_id > 0
    AND o.[Type] IN (''P'',''TF'',''FN'') and o.[Is_MS_Shipped] = 0
    order by [database], [stored procedure/function], [table]'
    FROM sys.databases
      WHERE 
      database_id > 4
      AND [state] = 0

    SET @sql = STUFF(@sql,1,11,'');

    EXEC sp_executesql @sql;


    Lenfinkel

    Tuesday, March 3, 2015 3:35 PM
  • One way to view the dependencies of an object is to use SSMS; navigate to the object, right click and select "view dependencies".  However this does not tell you what columns are being referenced by the Stored Procedure.

    One way to view what is being referenced by a stored procedure is to run something like

    select

    text from syscomments


    where

    id in (select id from sysobjects where name =[Your Object Name] And xtype in ('p','P')


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, March 3, 2015 3:54 PM