Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sequence of Database Objects Dependency - SQL 2005


Sequence of Database Objects Dependency - SQL 2005

Author
Message
msforumpost
msforumpost
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
Florian Reischl
Florian Reischl
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2255 Visits: 3934
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


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search