Find Sp's using tables of other db's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find Sp's using tables of other db's

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 12:10:03

Hi,

I need a query to find the sp's which are using other database tables

for eg: i have DB1, DB2 are two databases

and i have some sp's (stored procedures) in DB1 which are using tables of DB2.

For an instance I have table abc in DB2 and i need to find out which sp's in DB1 are using Table abc of DB2. I need to find this using a query...can any one help me...

Thanks in advance.....

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-08 : 12:13:48

Hello..

This code I am using was downloaded from Journey to SQL Authority (owned by Pinal Dave). I think it is what you are looking for. It is appropriate to give him proper credit.
[url]http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure[/url]

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

—-Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘%tablename%’
—-Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘%tablename%’

r&r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 12:17:26

[code]SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE PATINDEX('%DB2.dbo.abc%',definition)>0[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 12:18:52

quote:
Originally posted by revdnrdy

Hello..

This code I am using was downloaded from Journey to SQL Authority (owned by Pinal Dave). I think it is what you are looking for. It is appropriate to give him proper credit.
[url]http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure[/url]

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

—-Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘%tablename%’
—-Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘%tablename%’

r&r