How can I get the list of tables in all the stored procedure

Question

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?

Answer 1

This one works for me

DECLARE
    @sp nvarchar(517) --param datatype for [ schema_name. ] referencing_entity_name 
                      --https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referenced-entities-transact-sql?view=sql-server-ver16#arguments

SET @sp = N'<enter schema.SPROC name>'

-- Objects on which [@sp] depends
SELECT DISTINCT
    referenced_schema_name, 
    referenced_entity_name
FROM sys.dm_sql_referenced_entities(@sp, 'OBJECT')

Answer 2

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).

Answer 3

You can run these queries to find referenced or referencing object (stored proc in your case):

To find Referenced object:

DECLARE @sp sysname

SET @sp = N'<enter schema.SPROC name>'

SELECT
    SCHEMA_NAME(o.schema_id) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name,
    o.type_desc AS referencing_description,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referenced_id = OBJECT_ID(@sp)
    --AND o.type = N'P' -- if you are only interested in SQL_STORED_PROCEDURE
OPTION (RECOMPILE);
GO  

To find Referencing object:

DECLARE @sp sysname

SET @sp = N'<enter schema.SPROC name>'

SELECT
    SCHEMA_NAME(o.schema_id) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_description, 
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(@sp)
    --AND o.type = N'P' -- if you are only interested in SQL_STORED_PROCEDURE
OPTION (RECOMPILE);
GO