Sql-server – How to get the list of tables in all the stored procedure

sql server

I am trying to get the information about all the tables belongs to the stored procedure. I was able to get this information from below query. But, this query only gives information about the tables who are in the same database. What if my procedure is using the tables from other database separated by different schema? How can I modify my query to give information about Database name, schema name, table name of all the stored procedures in the given database. I would like to list the table who belongs to other database as well.

Query (SSMS – 2016)

  SELECT DISTINCT s.name,p.name AS proc_name, t.name AS table_name
  FROM sys.sql_dependencies d 
  INNER JOIN sys.procedures p ON p.object_id = d.object_id
  INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
  INNER JOIN Sys.schemas s on s.schema_id=p.schema_id
  ORDER BY proc_name, table_name

Can anyone please help?

Best Answer

Tables don't "belong" to stored procedures, but I think you want sql_expression_dependencies:

SELECT DISTINCT s.name, p.name, 
  db  = COALESCE(d.referenced_database_name, DB_NAME()),
  obj = COALESCE(d.referenced_schema_name, s.name) + N'.' 
        + d.referenced_entity_name
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.procedures AS p
  ON p.[object_id] = d.referencing_id
INNER JOIN sys.schemas AS s
  ON p.[schema_id] = s.[schema_id]
ORDER BY p.name, obj;

Of course this won't find dependencies that are constructed using dynamic SQL, and the schema references might be wrong in cases where you've explicitly left out the schema name in the query so it can be determined by the user's default schema (if you're doing that, please don't do that).

Related Question