Amsri


Guys,

I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.

For example

EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table

My truncate script should be

TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE

Is there any automated way to figure out parent and child tables and generate truncate script for the same.

Thanks



Re: Truncate database tables based on foreign key constraints

Philip Kelley


Your main problem is going to be that you cannot run the TRUNCATE TABLE command on a table that other tables reference with a foreign key--even if you disable [nocheck] all such foreign keys. You'll either need to drop the foreign keys, truncate the tables, and recreate the foreign keys, or write delete statements.

To determine the parent-child heirarchy throughout your database, the quickest way would be to work through the sys.foreign_keys system view. (This is for SQL 2005; similar views exist in SQL 2000 and 7.0, but I'm a bit out of practise with them.) It is possible to write a recursive CTE (again, only in SQL 2005) that "walks through" this table and lists out the parent/child relationships; I've done a few things like this, but haven't got one specific to your problem--but I dare say there are scripts out there "on the web" that will do this.

You would have to specifically pick out the lookup tables you did not want truncated/deleted, unless there is some way (naming conventions ) to differentiate them from the other tables.

I wrote the following script to work over individual tables, but it could possibly be used for your situation. When run in a database, it will list out information for that database, and the "DropFK" and "CreateFK" columns could be used to drop and recreate *all* foreign key constraints. This is NOT an ideal situation, as it does not work for mutli-column foreign keys, though it does make it clear (column FKColCount) which ones those are; also, it uses naming conventions that may not meet your needs. Even so, you could generate the scripts, adjust any mutiple column FKs drop and creates by hand, drop the FKs, truncate all but the lookup tables, and then recreate the FKs.

SELECT fk.name, fk.is_disabled, fk.is_not_trusted, fkc.constraint_column_id

,objc.name ChildTable

,coc.name ChildColumn

,objp.name ParentTable

,cop.name ParentColumn

,'ALTER TABLE ' + objc.name + ' with check check constraint ' + fk.name MakeFKTrusted

,'ALTER TABLE ' + objc.name + ' drop constraint ' + fk.name DropFK

,cast(fkcGrp.howManyFKCols as varchar(4))

+ case fkcGrp.howManyFKCols when 1 then '' else ' -- RECREATE script is invald!' end FKColCount

,'ALTER TABLE ' + objc.name + ' with check add constraint FK_'

+ objc.name + '__' + objp.name + ' foreign key (' + coc.name + ') references '

+ objp.name + ' (' + cop.name + ')' CreateFK

from sys.foreign_keys fk

inner join sys.foreign_key_columns fkc

on fkc.constraint_object_id = fk.object_id

inner join (select constraint_object_id, count(*) howManyFKCols

from sys.foreign_key_columns

group by constraint_object_id) fkcGrp

on fkcGrp.Constraint_Object_id = fk.object_id

inner join sys.objects objc

on objc.object_id = fk.parent_object_id

inner join sys.columns coc

on coc.object_id = objc.object_id

and coc.column_id = fkc.parent_column_id

inner join sys.objects objp

on objp.object_id = fk.referenced_object_id

inner join sys.columns cop

on cop.object_id = objp.object_id

and cop.column_id = fkc.referenced_column_id

I have to admit, all the options seem very awkward. Why not just script out the database (via SSMS or other utility), recreate it, and repopulat the (hopefully small) lookup tables

Philip