SQL Server, How to pull all references to tables and columns used in stored procedures from other databases

xiozqbni  于 2023-11-16  发布在  SQL Server
关注(0)|答案(1)|浏览(133)

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.

t98cgbkg

t98cgbkg1#

One option is to use sys.sql_expression_dependencies :

select 
    ProcedureSchema = schema_name(o.schema_id),
    ProcedureName = o.name,
    ReferencedSchema = schema_name(t.schema_id),
    ReferencedName = t.name,
    ReferencedType = t.Type_desc
from sys.sql_expression_dependencies xd
inner join sys.objects o
    on xd.Referencing_id = o.object_id
        and o.type = 'P'
inner join sys.objects t
    on xd.referenced_id = t.object_id

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

select top 1000 *
from sys.objects o
cross apply sys.dm_sql_referenced_entities(schema_name(o.schema_id) + '.' + o.name, 'OBJECT') r
where o.type = 'P'

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 an object_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.

相关问题