Find the tables used in a SP

Question

Example:
Create proc SQL
AS
Begin  

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.SSN,

            a.req ,

            c.VN ,

            b.PayRunDt,

            d.UpdateDt

         FROM

            dbo.Trv  AS a,

            dbo.Ver  AS b,

            dbo.VTrv  AS c,

            dbo.TrvVs  AS d

where a.id=b.id and b.id=c.id and c.id=d.id

Select * from #temp

END

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

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

Answer 1

Object dependency varies from version to version.

Here is SQL Server 2008 object dependency discussion with examples:

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

---

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

Answer 2

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

 SET NOCOUNT ON;

 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; 
   
 DECLARE
     @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) + '),
         OBJECT_SCHEMA_NAME(referencing_id,'
             + convert(varchar,@database_id) +'),
         OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '),
         referenced_server_name,
         ISNULL(referenced_database_name, db_name('
              + convert(varchar,@database_id) + ')),
         referenced_schema_name,
         referenced_entity_name
     FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';
   
     EXEC(@sql);
   
     DELETE FROM #databases WHERE database_id = @database_id;
 END;

 

Answer 3

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');

--Prashanth

Answer 4

it doesn't return anything...if you see the above example data is loading into temp table.

Answer 5

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.

--Prashanth

Answer 6

any other answers for my example?

---

ss