Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Find dependencies of an object across the database Expand / Collapse
Posted Thursday, July 15, 2010 2:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 26, 2014 3:42 AM
Points: 31, Visits: 91
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.

Post #952928
Posted Thursday, July 15, 2010 4:55 AM



Group: General Forum Members
Last Login: Yesterday @ 8:07 PM
Points: 14,543, Visits: 38,400
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.


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #952994
Posted Thursday, July 15, 2010 5:05 AM

Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, January 10, 2016 12:04 PM
Points: 683, Visits: 1,121
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 »

Add to briefcase

Permissions Expand / Collapse