Is there any way to list all tables in a correct order of parent child relation.

Question

Post reply

Is there any way to list all tables in a correct order of parent child relation.

Atik

SSC Veteran

Points: 288
More actions
March 28, 2006 at 3:45 am

#111502

I am developing an data import program. When you copy data from source database one has to know about your table sequence in target database. Otherwise it fails beacuse of foreign key parent child relations.

Is there any way to list all tables in a correct order of parent child relation.
i tried to find out from information_schema tables but it didnt help

Viewing 8 posts - 1 through 7 (of 7 total)

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

Vee Hall of Fame Points: 3028 More actions · Answer 1

Hi,

Try This PROCEDURE

-- Created By : VEERESH V NASHI (DBA)

-- Created ON : 04-02-2006

-- Purpose : To List Dependant and Reference Objects For a Given Relation

----------------

ALTER PROCEDURE dbo.prc_UTL_Dependent_Reference (@Table_Name SYSNAME , @Type CHAR(1)='R') WITH ENCRYPTION

AS

BEGIN

SET NOCOUNT ON

IF @TYPE='D'

SELECT DISTINCT OBJECT_nAME(FKEYID) AS THIS_TABLE, OBJECT_nAME(RKEYID)AS DEPENTDANT_ON FROM Sysforeignkeys --order by 2

WHERE constid in (select id from sysobjects where parent_obj = object_id(@Table_Name) and xtype='f')

IF @TYPE='R'

SELECT DISTINCT OBJECT_NAME(RKEYID) THIS_TABLE ,OBJECT_NAME(fKEYID) REFFERRED_BY FROM SYSREFERENCES

WHERE OBJECT_NAME(RKEYID)=@Table_Name order by 1

SET NOCOUNT OFF

END

Example:

TO GET ALL Dependancies for the table 'tbl_People'

execute dbo.prc_UTL_Dependent_Reference 'tbl_People','D'

TO GET ALL Reference for the table 'tbl_People'

execute dbo.prc_UTL_Dependent_Reference 'tbl_People','R'

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 2

Hi,

Use this Query in your related Db :

select object_name(constid),object_name(fkeyid),object_name(rkeyid)

from sysreferences

Regards,

AMIT GUPTA..

Atik SSC Veteran Points: 288 More actions · Answer 3

thank you very much for your explanations

These informations really help to find dependencies.

But my problem is how to find that total hierarchy from these lists

may be one should iterate many times to build a hierachy

thanks anyway it was valuable

Brian Munier SSCrazy Points: 2875 More actions · Answer 4

Since the goal of your program is data import, can you drop constraints, load the data and then re-apply the constraints? (i.e. a bulk load during a "down time" for the DB) Is your data referentially in tact?

If yes, then use enterprise manager, and script the database including all constraints with the drop commands, save the file and pull all the drop constraint commandss to a drop constraints script e.g. db_drop_constraints and all the add constraints to an add constraints script e.g. db_add_constraints.

The added benefit of this is a faster load time, but your add constraints will bomb out if you have bad data.

The other method I can think of is to use dts packages to script the migration.

I agree that figuring out the scripting hierarchy is difficult. Using Enterprise Manager show dependancies, presuming you know a top most level you can probably get all related with a number of how many levels down it is, which would then say load in numerical order all the tables.

The other thing you can look for is all tables that do not have foreign keys on them, if you look in design table/relationships the FK is indicated as an infinity signe (sideways 8) when it refers to a table. Start by scripting all tables that do not refer to another one first as they will definately not fail. That should eliminate some of the work.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Is this a generic program or specifically for your databases? If specific, figure out the hierarchy and load it from an xml/ini file.

If generic, license the depends code from Red gate software. Or buy their data compare product.

Atik SSC Veteran Points: 288 More actions · Answer 6

I lltry to answer all your questions.

fist i have redgate software. It is really powerfull but only when two database are identical on schema. and readgate software fails on data table sequence if you dont fix your self in xml files.

DTS may not be used if schema is not identical. It fails if sequence of

primary keys are not identical.

Drop constrains is not a professional way to do and have a bigger responsiblity on building schema.

My import program is universal you have a referance database as target and you select a source to immigrate data from it. On that way you can clean dirty data in an old database and immigrate data into new schema. But ofcourse i do today by arranging table sequence

manually. I wish i could do that automattically.

Thank you very much for your comments

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 7

I am shooting from the hip here, but I did something similar a while back.

This is how I remember it

1. Get the list of table reference from sysreferences

2. Compare your Table list with your references list

If TabA references no table, it is a level 1 i.e it is a base /primary table

Tables that only reference Level1 tables are level 2

In Essence, a table's level is 1 + the lowest level it references,

so if it references three level 1 tables and one Level2 table, then it is a level3 Table.

I realise it's a bit vague but hope it helps.