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


    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:

    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








    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

     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;

     

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


    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.

    --Prashanth

    Wednesday, July 09, 2014 4:14 PM