CODEWITHSUNDEEP
sql-serverdatabase
Dhaval
Dhaval

Reputation: 2861

How to know which stored procedure has cross database query

I want to know which stored procedure has cross database/ linked server call.

I know i can do some kind string search with database name being string to search. One of the example of it as below 

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE m.definition Like '%{{DBNAME}}%';

but if possible then i do not want to go for string search so is there any other way to find all procedure which might have cross database/ linked server ?

Upvotes: 0

Views: 1023

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

There is a system view sys.sql_expression_dependencies that provides exactly this information:

select d.*
from sys.sql_expression_dependencies d
where d.referenced_database_name is not null
    and d.is_ambiguous = 0;

Pay attention, however, that some of the references might be "fake", or rather not what you expect. For example, XML methods are also listed in it, which might cause some confusion.

Upvotes: 1

Related Questions