Sequence of Database Objects Dependency - SQL 2005

  • Hi All,

    I want to know , as to how we can find the order of dependency in SQL Server 2005 for the objects. I currently have a database and From this EXISTING DATABASE, I have created

    1. Database Creation scripts

    2. Table creation Scripts

    3. Creation scripts for Views

    4. Creation Scripts for Functions and Stored Procedures.

    Now ,during deployment of my scripts onto the production server, I wanted to let you know the sequence in which the scripts get executed. I believe the order should be something like

    1. Create Database script

    2. Table Creation Scripts

    3. View creation scripts

    4. Scripts for creating functions and stored procedures.

    Since there are many tables , and some tables reference the other table , due to primary key - foreign key relation , I need to make sure that I create the base most table first and then the other tables in the sequential order.

    Now given the fact that I already have the database now, how do I find the dependency of one object over the other and the first database object to be created. I know that there is a stored procedure sp_MSdependencies, but that gives the oSequence as 1 for all tables. Can anyone help me figure out the sequence.

    Thanks in Advance

  • Hello

    The problem with the tables, PKs and FKs might be not so difficult:

    * Create all tables

    * Create all the primary keys (this might be done within the first step.

    * Create all FKs. Now all tables are available and all the FKs can be created

    It becomes more tricky with the views and (especially) the functions and procedures. Maybe have a look to ths sys.sql_dependencies:

    SELECT *, OBJECT_NAME(object_id) origin, OBJECT_NAME(referenced_major_id) destination

    FROM sys.sql_dependencies

    With this information you can try to script the objects by dependency. But be carefull that there are no circular references!

    Greets

    Flo

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

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