How to find all tables depends to stored procedure-CodePudding

I want to find all tables used in stored procedures but it only gives me the tables that are in the database that I'm just using. Is there a solution to find all tables used in stored procedures, which are in other databases (in the same server)?

I try this:

SELECT DISTINCT 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
WHERE p.name like '%sp_example%'
ORDER BY proc_name, table_name

The procedures I need to analyze contain tables from different databases, but the code above gives me only results from one database.

CodePudding user response：

First of all, sys.sql_dependencies is deprecated. Instead, you should use sys.sql_expression_dependencies. In it, you will find 4-part names of referenced objects (this includes objects referenced via linked server, for example).

Second, any object_id in SQL Server only makes sense within its database. If you are looking for anything outside of your current DB, don't join tables by these identifiers - use object names instead.