November 5, 2007 at 1:26 pm
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
November 5, 2007 at 2:42 pm
Sorry to say. but if there is a foreign key in place, TRUNCATE TABLE will not work. It's one of the stated limitations of the TRUNCATE TABLE syntax. You would need to use DELETE FROM tablename instead.
On the other hand - there is a feature you MIGHT care to look into, which can be set up on foreign key constraints called "CASCADE DELETE": you delete the parent, and the children get whacked as well. (Kinda of like Tony Montana, exclaiming "I wanna him DEAD...I wanna his family, DEAD...." etc...)
As you can imagine - you'd want to think that one through CAREFULLY before doing it. Only a few occasions I've ever used it in, and it's usually for adjunct data that was fairly inconsequential.
Otherwise - script them in order, and handle your deletes manually. Your data model shouldn't be changing so fast that you can't establish a standard deletion order; if it is changing that fast - you have much bigger problems than even this....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2007 at 3:36 pm
Actually there is a way to do it .. sort of. I ran into this at a job where we were supporting a dozen or so version of the same software.
Create a table and populate it with the names of all of your other tables. Then create a recursive SP to go through it and check for anything that has no references that arn't already given an "order" value. Ie you have
Table1
Table2 - Relates to Table1
Table3 - Relates to Table1
Table4 - Relates to Table2
After the first pass you have
TableName OrderVal
Table1 1
Table2
Table3
Table4
After the second pass you have
TableName OrderVal
Table1 1
Table2 2
Table3 2
Table4
And after a 3rd pass you have
TableName OrderVal
Table1 1
Table2 2
Table3 2
Table4 3
After that you create your script based on that sort order. Your script will look something like this
TRUNCATE Table1
ALTER TABLE Table2 DROP CONSTRAINT fk_Table1_Table2
ALTER TABLE Table3 DROP CONSTRAINT fk_Table1_Table3
etc
TRUNCATE Table2
TRUNCATE Table3
TRUNCATE Table4
ALTER TABLE Table2 ADD CONSTRAINT fk_Table1_Table2 .......
etc
It shouldn't take very long because by the time you are creating your constraints again the tables you are working with are mostly empty.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 5, 2007 at 3:43 pm
Although come to think of it if you are going to generate a script to drop all of the foreign keys, truncate the tables, then restore the foreign keys it really doesn't matter what order you do them in.
Just make sure you exclude your lookup tables from the list 😀
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 5, 2007 at 5:01 pm
ok how about this:
get all the tables in FK hierarchy order; truncate tables with no FK, and for teh rest of the tables that feature foreign keys delete and then reset the identity for tables WITH the foreign key hierarchy.
add a WHERE statement to exclude lookup tables. set nocount on
declare @level tinyint
set @level = 0
create table #tables (
id int not null primary key clustered,
TableName varchar(255) not null,
Level tinyint not null)
insert into #tables (id, TableName, Level)
select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0
from sysobjects where xtype = 'U' and status > 0
while @@rowcount > 0 begin
set @level = @level + 1
update rt set Level = @level
from #tables rt
inner join sysreferences fk on fk.rkeyid = rt.id
inner join #tables ft on ft.id = fk.fkeyid
where ft.Level = @level - 1
end
print 'USE ' + DB_NAME() + '
'
select 'TRUNCATE TABLE ' + TableName from #tables where level = 0 and TableName Not in('City','State')
select 'DELETE ' + TableName + '; DBCC CHECKIDENT (''' + TableName +''', RESEED) ;' from #tables where level > 0 and TableName Not in('City','State') order by level
drop table #tables
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply