0

our company is planning to administer the report server database and the requirement is to get the list of the tables used in stored-procedures in the productions server database.Is there any query/function to retrieve this information ?

2
  • If your procedures use dynamic code like select @Tname = 'MyTable' + datename(year, getdate()); exec('select * from ' + @Tname) it's not possible to determine if MyTable2018 is used or not
    – sepupic
    Jan 8, 2018 at 10:41
  • 1
    But you can try to use sys.sql_expression_dependencies and sys.sql_modules.definition like ...
    – sepupic
    Jan 8, 2018 at 10:43

2 Answers 2

4

If you use dynamic SQL to construct table names (or accept them as arguments), or use nested views, it becomes very difficult to automate this. However if you stick to traditional T-SQL and proper references you can get there with this:

DECLARE @procid int = OBJECT_ID(N'dbo.procedurename');

;WITH src AS 
(
  SELECT name = 
      COALESCE(QUOTENAME(d.referenced_server_name)   + N'.', N'')
    + COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
    + QUOTENAME(d.referenced_schema_name) + N'.'
    + QUOTENAME(d.referenced_entity_name)
  FROM sys.sql_expression_dependencies AS d
  WHERE d.referencing_id = @procid
)
SELECT name FROM src GROUP BY name;

And if you just want a catalog of all references, you can do this:

;WITH src AS 
(
  SELECT [procedure] = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name),
      ref = 
      COALESCE(QUOTENAME(d.referenced_server_name)   + N'.', N'')
    + COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
    + QUOTENAME(d.referenced_schema_name) + N'.'
    + QUOTENAME(d.referenced_entity_name)
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
  ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE o.[type] = N'P'
)
SELECT [procedure],ref 
  FROM src
  GROUP BY [procedure],ref;
-1
; WITH MYTABLES_CTE  (SchemaName, ProcedureName, ServerName, DatabaseName, SchemaName2, TableName, ROW_CNT, TXT)
AS
 (
  SELECT  V.SchemaName 
        , V.ProcedureName
        , V.ServerName
        , V.DatabaseName
        , V.SchemaName2
        , V.TableName
        , V.ROW_CNT
        , CAST(V.SchemaName2 + '.' + V.TableName AS VARCHAR(MAX))

    FROM #VT_TEMP V
    WHERE ROW_CNT = 1

    UNION ALL

      SELECT  V.SchemaName 
        , V.ProcedureName
        , V.ServerName
        , V.DatabaseName
        , V.SchemaName2
        , V.TableName
        , V.ROW_CNT
        , CAST(TXT  + ', ' + V.SchemaName2 + '.' + V.TableName AS VARCHAR(MAX))

   FROM #VT_TEMP V INNER JOIN MYTABLES_CTE C
   ON V.SchemaName = C.SchemaName
   AND V.ProcedureName = C.ProcedureName
   AND V.ROW_CNT = C.ROW_CNT+1
)


SELECT DISTINCT VV.SchemaName 
        , VV.ProcedureName
        , VV.ServerName
        , VV.DatabaseName
        , VV.SchemaName2
        , VV.TableName
        , VV.ROW_CNT
        , VV.TXT
 FROM MYTABLES_CTE VV INNER JOIN ( SELECT ProcedureName
                                      , MAX(ROW_CNT) AS MX_CNT
                                 FROM MYTABLES_CTE C
                                 GROUP BY ProcedureName
                               ) SC
 ON VV.ProcedureName = SC.ProcedureName
 AND VV.ROW_CNT = SC.MX_CNT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.