Find the tables used in a SP


  • Example:
    Create proc SQL

    CREATE TABLE #temp


             Name varchar(14),

             Num varchar(9),

             SSN varchar(9),

             req int,

             VN varchar(12),

             PayrunDt smalldatetime,

             UpdateDt smalldatetime

          INSERT #temp

             SELECT DISTINCT

                a.Name ,

                a.Num ,


                a.req ,

                c.VN ,




                dbo.Trv  AS a,

                dbo.Ver  AS b,

                dbo.VTrv  AS c,

                dbo.TrvVs  AS d

    where and and

    Select * from #temp


    Question :I have X numbers of SP as shown in the above example(with different select queries loading data to x number of temp tables). i have to find the table and columns involved in the SP. can you please help me with the query? so that i can use it to for other SP's?

    tried using the below Q, no luck

     SP_Name =,
     Table_Name =
     FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON =
     INNER JOIN sys.sysobjects OO ON = D.depid
     WHERE O.xtype = 'P' and in ('sp names')

    Tuesday, July 08, 2014 9:47 PM

All replies

  • Object dependency varies from version to version.

    Here is SQL Server 2008 object dependency discussion with examples:

    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Tuesday, July 08, 2014 10:02 PM
  • I used the below script, almost returns what I am looking for but not the column name used in the proc, can you please help me on that?

    create PROCEDURE [dbo].[get_crossdatabase_dependencies] AS


     CREATE TABLE #databases(
         database_id int,
         database_name sysname

     -- ignore systems databases
     INSERT INTO #databases(database_id, database_name)
     SELECT database_id, name FROM sys.databases
     WHERE database_id > 4; 
         @database_id int,
         @database_name sysname,
         @sql varchar(max);

     CREATE TABLE #dependencies(
         referencing_database varchar(max),
         referencing_schema varchar(max),
         referencing_object_name varchar(max),
         referenced_server varchar(max),
         referenced_database varchar(max),
         referenced_schema varchar(max),
         referenced_object_name varchar(max)

     WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
         SELECT TOP 1 @database_id = database_id,
                      @database_name = database_name
         FROM #databases;
         SET @sql = 'INSERT INTO #dependencies select
             DB_NAME(' + convert(varchar,@database_id) + '),
                 + convert(varchar,@database_id) +'),
             OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '),
             ISNULL(referenced_database_name, db_name('
                  + convert(varchar,@database_id) + ')),
         FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';
         DELETE FROM #databases WHERE database_id = @database_id;


    Wednesday, July 09, 2014 3:04 PM
  • Why don't you try built-in stored proc

    sp_depends <StoredprocedureName>
    SELECT referenced_entity_name,referenced_minor_name,*FROM sys.dm_sql_referenced_entities ('<ProcName>', 'OBJECT');

    Wednesday, July 09, 2014 3:24 PM
  • it doesn't return anything...if you see the above example data is loading into temp table.
    Wednesday, July 09, 2014 3:36 PM
  • Are you trying to list the #temp table columns? The temp table scope is limited to session. You can't list outside of the session.

    I've tried the above two SQL's to list the object and its table and columns.


    Wednesday, July 09, 2014 4:14 PM