Truncate All Tables in Oracle and SQL Server

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.

Leave a Reply

Your email address will not be published. Required fields are marked *