Find dependencies of an object across the database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Find dependencies of an object across the...

Find dependencies of an object across the database

Rate Topic

Display Mode

Topic Options

Author

Message

iam777here4u

Posted Thursday, July 15, 2010 2:57 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:06 AM
Points: 22, Visits: 56

Dear All,

Please help me to find the dependencies of an database object across all other databases. I am using SQL 2000. I have selected an object from one database. I want all dependent object of this selected object among all databases and also the list of objects which the selected object is dependent to.

Is it okay trusting SP_Depends? It seems not giving correct answer.

Regards

Post #952928

Lowell

Posted Thursday, July 15, 2010 4:55 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:36 PM
Points: 11,627, Visits: 27,690

sp_depends is 100% accurate for tables functions and views. you cannot create any of those things with a dependency to another object unless the object exists.
because of the way procedures are allowed to be created, it can be incorrect for procedures if
1: the proc was created BEFORE the objects it uses were created.
2. the proc uses dynamic SQL.

if you recompile your procedures, you can fix #1 as the ALTER PROC syntax will put the dependencies back in for you if they were missing, but there's no way i know of to accurately get dynamic sql dependencies.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #952994

Jim McLeod

Posted Thursday, July 15, 2010 5:05 AM

Say Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 4:40 AM
Points: 687, Visits: 1,074

I've recently been looking at a similar problem - approximately 25 databases all with links into other databases, and we can't upgrade them all to 2008 at once. sp_depends is not accurate enough when dealing with objects in other databases, or via linked servers.

Our solution has been to restore all databases into a development 2008 environment and query the new sys.sql_expression_dependencies view. It's not perfect, as Lowell mentioned, as it can't detect dynamic SQL, but it's a lot better than the 2000 version of sp_depends.

Post #953000

« Prev Topic | Next Topic »

Permissions