December 4, 2008 at 2:34 am
How to sort the tables in a database by foreign key constraint.
So if one can have insert scripts for all the tables in the database and is having a batch file to execute the scripts , is there any way to order tables such that no foreign key error can occur during execution of the batch file.
We can get this information by using following stmts,
sys.objects
sysforeignkeys
sys.tables
etc.
December 4, 2008 at 10:17 am
I've used this script for years to figure out the insert/delete order of tables with foreign keys. I haven't had time to change the SQL 2000 system table names yet, but it still works in SQL 2005 because the tables were included as views for backward compatibility.
I don't remember where I got the script or I'd credit the original author.
Greg
December 5, 2008 at 6:24 am
Thanks ,
The script is able to differentiate between the hierarchy levels but in a specific hierarchy level e.g. 2 it is not able to order it correctly.
One level can have tables with references in same hierarchy.
So it must sorted within the hierarchy also.
Once again thanks.
I will use this script appropriately.
December 5, 2008 at 7:51 am
try this one; i once had some databases that had circular foreignkeys..i had to find a way to really put them in order.
see if this works for you:
SET NOCOUNT ON
DECLARE
@Level INT,
@MovedToNewLevel INT,
@sql varchar(1024),
@err varchar(125),
@LastBatch int
CREATE TABLE #Hierarchy
(FKLevel INT,
TblName VARCHAR(100),
id Numeric
)
-- Populate the table
INSERT INTO #Hierarchy
select 0 AS 'FKLevel', name as 'TblName',id
FROM sysobjects where xtype='U' and
id not in (select rkeyid from sysforeignkeys) and
id not in (select fkeyid from sysforeignkeys)
INSERT INTO #Hierarchy select 1 AS 'FKLevel', name as 'TblName',id
FROM sysobjects where xtype='U' and
id not in (select id from #Hierarchy) and
id in (select rkeyid from sysforeignkeys) and
id not in (select fkeyid from sysforeignkeys)
INSERT INTO #Hierarchy select 2 AS 'FKLevel', name as 'TblName',id
FROM sysobjects where xtype='U' and
id not in (select id from #Hierarchy) and
id in (select fkeyid from sysforeignkeys)
-- Set the variables
set @Level=2
set @MovedtoNewLevel=1
WHILE @MovedtoNewLevel 0
BEGIN
set @LastBatch=@MovedtoNewLevel
set @sql='update #Hierarchy set FKLevel = FKLevel + 1 where FKLevel=' + CAST(@Level as varchar) + ' and'
set @sql=@sql + ' id in (select fkeyid from sysforeignkeys where fkeyidrkeyid and'
set @sql=@sql + ' rkeyid in (select id from #Hierarchy where FKLevel=' + CAST(@Level as varchar) + ' ))'
exec(@sql)
SET @MovedtoNewLevel = @@Rowcount
set @err='#ID''s yet to be processed :' + convert(varchar,@MovedtoNewLevel)
--'BANDAID TO WORK AROUND CIRCULAR FK REFERENCES, WHERE EVENTUALLY,
--TABLEA REFERS TO TABLEQ AND TABLEQ REFERS TO TABLEA
if @LastBatch=@MovedtoNewLevel
BEGIN
select top 1 @err= TblName from #Hierarchy WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )
set @err = 'possible Circular FK''s found, arbitrarily changing ' + @err + ' to break circular reference.'
--RAISERROR (@err,1,1)
set rowcount 1
UPDATE #Hierarchy SET FKLevel = FKLevel - 1
WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )
set rowcount 0
END
--RAISERROR(@ERR,1,1)
SELECT @Level = @Level + 1
End
select * from #Hierarchy order by FKLEVEL
Lowell
December 8, 2008 at 12:17 am
Simply great reply.
Thanks very much.
December 8, 2008 at 6:17 am
glag we could help; what was your project doing that you needed them in FK order? your answer might help someone else with the same issue.
Lowell
December 9, 2008 at 7:48 am
At the time of project delivery the client wants insert scripts of the database which contains some master data and semi - transactional data.
So by executing a batch file client can run these scripts with no foreign key error.
June 20, 2009 at 2:22 am
hI ssCRAZY
I just ran your script in northwind to get a feel. I request you please suggest if this script can be run for deleting records in a database after identifying the relationships
June 20, 2009 at 6:28 am
that would be the easy part; since the temp table #Hierarchy has the tables in the right order, change the last SELECT statement to this:
SELECT
CASE WHEN FKLevel=0 THEN ' TRUNCATE TABLE '
ELSE ' DELETE FROM '
END + '[' + TblName + ']'
from #Hierarchy order by FKLEVEL DESC
example results:
DELETE FROM [ChildTable]
DELETE FROM [ClaimTable]
TRUNCATE TABLE [Log]
TRUNCATE TABLE [EXAMPLE]
TRUNCATE TABLE [MYCDRIVE]
Lowell
June 20, 2009 at 8:40 am
hi Thanks for your repl
Can you please tell me how to modify the script to delete records from master and check if any dependent child will be afftected.
The deletion will be based on user selected table and column and based on user specified column value
Your help will be higly appreciated
June 20, 2009 at 9:04 am
lakshminarasimhanv (6/20/2009)
hi Thanks for your replCan you please tell me how to modify the script to delete records from master and check if any dependent child will be afftected.
The deletion will be based on user selected table and column and based on user specified column value
Your help will be higly appreciated
run the script on any master database...you'll see it already excludes system tables.
read the script closely, and you'll see this line int here:
FROM sysobjects where xtype='U'
that section selects only user created tables, and not all tables.
since the script only prints the command you could use, and never executes the command, it's safe to run against any database. run the script a couple of times and get a feel for what it does, I think you'll see it meets your requirements.
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply