Get the list of tables used in a stored-procedure for a database

Question

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 ?

Answer 1

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;