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

Sequence of Database Objects Dependency - SQL 2005 Expand / Collapse
Posted Monday, March 16, 2009 1:09 AM


Group: General Forum Members
Last Login: Wednesday, September 8, 2010 11:23 AM
Points: 21, Visits: 88
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
Post #676289
Posted Monday, March 16, 2009 5:43 AM



Group: General Forum Members
Last Login: Thursday, February 5, 2015 1:08 AM
Points: 1,893, Visits: 3,934

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!


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #676381
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse