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?