Post reply

Is sys.sql_dependencies important?

  • March 3, 2008 at 12:33 pm

    #203815

    I'm working on migrating a database from 2000 to 2005. After restoring a backup to 2005, I realized there was a direct reference to the database name in some of the stored procs, and it wasn't needed any more. So I generated scripts for all stored procedures, cleared out the text string that I needed, and ran the entire drop/create script. But I got a handful of errors like this:

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'some_sp_here'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    All of the stored procs were created, so it *looks* like I'm good. I can go and rearrange the creation script I think so I don't get these dependency errors, but there's like ~450 scripts being created, and it'd be a pain to cut and paste to get things in the right order. Is this even something I need to be concerned about? Is sys.sql_dependencies important?

  • March 3, 2008 at 8:02 pm

    #784750

    You should be all set as long as the script does create the needed objects later on.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 4, 2008 at 6:14 am

    #784883

    Jack's right.

    The table you asked about is where SQL Server tries to maintain the dependency chain between objects (although it doesn't do a great job at it). It's important and it's not.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 4, 2008 at 6:14 am

    #784882

    You should be good as long as you have created the dependent object at a later point of the script. It will associate itself if the object is there.

    -Roy

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

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