Find dependencies of an object across the database

  • 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

  • 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


    --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!

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

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