sam (sa*****@hotmail.com) writes:
I have too many tables in the database that I could not check the
denpency one by one. I am looking for a SQL statement that looks
through the system tables such as sysobjects and others to return the
table names in the proper order.
First do this:
SELECT 'ALTER TABLE ' + object_name(parent_obj) +
' NOCHECK CONSTRAINT ' + name
FROM sysobjects
WHERE xtype = 'F'
ORDER BY object_name(parent_obj)
Copy and paste into query window and run. This disables all foreign keys.
Second, bulk load your files in any order.
Third, run:
SELECT 'ALTER TABLE ' + object_name(parent_obj) +
' WITH CHECK CHECK CONSTRAINT ' + name
FROM sysobjects
WHERE xtype = 'F'
ORDER BY object_name(parent_obj)
This reenables the constraints and also checks that data satisfies the
constraints. If you change WITH CHECK to WITH NOCHECK, the constraints
will be enabled without checking. However, in this case, SQL Server
will mark the constraint as "not trusted", which means that the optimizer
will not consider them when building query plans.
(And yes that syntax is really WITH CHECK CHECK.)
I should also add that this is good for Sam, because he has an empty
database. If you have a similar problem for a live database, you need
to be more discriminate, if there are other operations going on in
parallel with your bulk-load.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
