How to find all procs in a DB which cross reference other databases

Question

How to find all procs in a DB which cross reference other databases

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
July 4, 2017 at 9:17 am

#337920

Hi All
I need to find all procs from a database which reference tables from another databases. all my DB has just one schema :dbo
so e.g. if I have a proc Proc1 in DB1 and it refres to some table in DB2, it should come in my list.
I thought of using this query:
select distinct object_name(id) from sys.syscomments where text like '%.dbo.%'
But I am just thinking if there could be a better way or approach to get this list?
thanks
sk
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

Try this & see whether it helps:

SELECT ObjName = OBJECT_NAME(referencing_id) , referenced_database_name FROM sys.sql_expression_dependencies WHERE referenced_database_name IS NOT NULL;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

Here's one way:

SELECT p.name, sed.referenced_database_name, sed.referenced_schema_name, sed.referenced_entity_name FROM sys.procedures p INNER JOIN sys.sql_expression_dependencies sed ON p.object_id = sed.referencing_id WHERE sed.referenced_database_name IS NOT NULL ;

This will show you all the procedures in the current database that reference objects in other databases, with some caveats.

First, this just shows you direct references; it won't show you that procedure A calls procedure B, which then depends on an object in another database.
If desired, though, the query could be modified to show such chains of dependencies.

Second (somewhat similar, actually), if you use any dynamic SQL or even simply EXECUTE some constant string that is a T-SQL command (which isn't even dynamic), references from those commands will not show up here.

You could try to parse all the text in the stored procedures to get those, but even that won't be guaranteed to capture everything (imagine a case of dynamic SQL where the object reference is built over multiple lines using concatenation into a variable, for example).

Unless you make extensive use of dynamic SQL, though, this should be a good start.

Cheers!

Edit: Heh, my slowness to craft responses strikes again. Phil beat me to the general idea. One of these days I'll remember to always refresh the topic before I post 🙂

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

Jacob Wilkins - Tuesday, July 4, 2017 10:08 AM
Edit: Heh, my slowness to craft responses strikes again. Phil beat me to the general idea. One of these days I'll remember to always refresh the topic before I post 🙂

Whereas I bashed my answer out in a couple of minutes, you took the time to produce something of quality ... that's not how I'd define slowness.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Super Cat SSCertifiable Points: 7334 More actions · Answer 4

Me for 1. I don't care who got there first. I got a useful script I wasn't aware I needed until now.
NICE 1 and a good question S_KUMAR_S.