I am attempting to get a list of all tables and columns being referenced within every stored procedure within a certain database ( DatabaseA
).
I have the following script written that accomplishes this just fine from what it appears first glance:
SELECT DISTINCT
O.name SP_Name, T.name Table_Name, c.name Field_Name
FROM
sys.sysdepends D
JOIN
sys.sysobjects O ON O.id = D.id
JOIN
sys.sysobjects T ON T.id = D.depid
JOIN
sys.columns C ON C.column_id = d.depnumber
AND C.object_id = D.depID
WHERE
O.xtype = 'P'
So this appears to pull in all table and column references for DatabaseA
's stored procedures, but only for DatabaseA
's tables.
How can I get it to reference all other databases that may be called within these stored procedures? All the other databases reside on the same server.
1条答案
按热度按时间t98cgbkg1#
One option is to use
sys.sql_expression_dependencies
:You could also use
sys.dm_sql_referenced_entities
which is a table-valued function which takes in an object. It gives you some additional information (such as how various columns are used).Be aware however, if you want to target cross database references, using object_ids to join to, say,
sys.objects
as I've shown above will not work. Since anobject_id
in one database does not equate to the same object in another database. In that case, you'll need to use the columns surfaced by either the dmv or function e.g.referenced_database_name
,referenced_schema_name
,referenced_entity_name
,referenced_minor_name
.Also, if any of those references are not properly qualified (e.g. if someone wrote
inner join OtherDatabase..SomeTable
you'll run into problems because it won't actually parse the missing name part.)So in short, these approaches have a few caveats, but are about as good as you're going to get.