Relationship finding script

Question

Post reply

Relationship finding script

karthik M

One Orange Chip

Points: 29139
More actions
August 29, 2007 at 10:36 am

#118094

Hi All,
I am working in DataMigration Project(Sybase12.5.3 to SqlServer200).
Sqlserver DBA has created the database with relationships as in sybase. Now i have to create DTS Package to move data from sybase to sqlserver. It is not an issue. But while creating a new DTS package i should take care of the relationship.
for example we have 5 tables namely A,B,C,D,E
E - depends table D,B
D - depends table A
C - depends table A,B
A - depends B
As per the above relationship , DTS Package should copy the data by the following sequence.
1. Table B
2. Table A
3. Table C and D
4. Table E
There are around 62 tables with relationship. I need a script to find out the relationship order as mentioned above.
Please anybody help me to get the script.

Regards
Karthik

karthik

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Look at sysdepends in BOL and you shoul b able to come up with something.

Lowell SSC Guru Points: 323518 More actions · Answer 2

I typically use this, which puts them in the desired order, unless you have some circular dependancies where table A references table B, and eventually a chain of them point back to teh top.

CREATE TABLE #HIERARCHY (TYPE int, ONAME varchar(517), OWNER varchar(517), SEQ int)

INSERT #HIERARCHY

EXEC sp_msdependencies @intrans = 1

DELETE FROM #HIERARCHY WHERE ONAME IN

(SELECT NAME FROM sysobjects WHERE sysobjects.xtype in ('P','FN','IF','TF'))

SELECT * FROM #HIERARCHY

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!