I was working with another developer today, and he wanted to experiment and see if running Entity Framework generator on a blank database takes longer than the one with a lot of data. We wanted to truncate all the tables. You cannot truncate a table with that participates in a foreign key constraint. SO, together we came up with a script that generates a script to achieve the task. I wanted to blog about it for myself, in case the same thing comes up again.
SELECT
‘Alter Table ‘ || c.table_name || ‘ disable constraint ‘ || c.constraint_name || ‘;’
FROM user_constraints p
JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = ‘P’ OR p.constraint_type = ‘U’)
AND c.constraint_type = ‘R’
union all
select ‘truncate table ‘ || table_name || ‘;’
from user_tables
union all
SELECT
‘Alter Table ‘ || c.table_name || ‘ enable constraint ‘ || c.constraint_name || ‘;’
FROM user_constraints p
JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = ‘P’ OR p.constraint_type = ‘U’)
AND c.constraint_type = ‘R’;
Of course, why would we want to forget SQL Server. Here is the same script for SQL Server
select ‘ Alter table ‘ + sys.tables.name + ‘ NOCHECK CONSTRAINT ‘ + sys.foreign_keys.name from sys.foreign_keys
inner join sys.tables on sys.tables.object_id = sys.foreign_keys.parent_object_id
union all
select ‘truncate table ‘ + name
from sys.tables
union all
select ‘ Alter table ‘ + sys.tables.name + ‘ CHECK CONSTRAINT ‘ + sys.foreign_keys.name from sys.foreign_keys
inner join sys.tables on sys.tables.object_id = sys.foreign_keys.parent_object_id
Enjoy.