March 28, 2006 at 3:45 am
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
March 28, 2006 at 4:19 am
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'
March 28, 2006 at 4:41 am
Hi,
Use this Query in your related Db :
select object_name(constid),object_name(fkeyid),object_name(rkeyid)
from sysreferences
Regards,
AMIT GUPTA..
March 28, 2006 at 6:36 am
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
March 28, 2006 at 7:39 am
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.
March 28, 2006 at 8:51 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 28, 2006 at 11:56 pm
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
March 29, 2006 at 12:31 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply