SQL Server truncate table - drop and recreate FK constraints script

Question

Hello,

I'm writing small application (in c#) which helps me to truncate tables in SQL Server 2005/08. In order to truncate table I think I need to do this:

• drop all FK constraints from table,
• truncate the table,
• recreate all previously deleted constraints.

Can someone help me to create such a script, or point me where I can find some clues?

Regards

Answer 1

In Enterprise Manager:

1. Select the table,
2. Right-click the table, choose All Tasks -> Generate SQL Scripts,
3. Un-tick Generate the Drop and Generate the Create,
4. Click over to the Options tab,
5. Tick all of the items beneath Table Scripting Options
6. Click back over to the General tab,
7. Click Preview,
8. Copy the content to a Notepad,
9. You now have all of your script for re-creating the indexes / keys / constraints.

With a bit of massaging of this chunk of script, you can make your drop constraint and drop index statements. Drop any clustered index last, or else you'll be rebuilding the other indexes as you go along.

Execute your drops, truncate the table, then execute the creates that you scripted out.

Also: perform a database backup right after you've done this, as any truncate table statement puts the transaction log into an uncertain state, as truncate table statements aren't logged.

Answer 2

Well, you could do this from your application:

• run a SQL command on your existing database to find all foreign key constraints
• from that list of foreign key constraints, create two scripts
  • one to drop all existing foreign key constraints (before you truncate the tables)
  • a second one to re-create the foreign key constraints after you've truncated the tables

You can do this by inspecting the system catalog view.

This query here will give you a list of all foreign key constraints:

select
    fk.name,
    object_name(fk.parent_object_id) 'Parent table',
    c1.name 'Parent column',
    object_name(fk.referenced_object_id) 'Referenced table',
    c2.name 'Referenced column'
from 
    sys.foreign_keys fk
inner join
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
inner join
    sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
inner join
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id

By combining these elements, you can create the list of DROP CONSTRAINT commands to be run before the truncation of the tables:

select
    'ALTER TABLE dbo.' + object_name(fk.parent_object_id) + 
    ' DROP CONSTRAINT ' + fk.name
from 
    sys.foreign_keys fk

and you can also create the ALTER TABLE scripts to be run after the truncating to restore the foreign key relationships.

select
    'ALTER TABLE dbo.' + object_name(fk.parent_object_id) + 
    ' ADD CONSTRAINT ' + fk.name +
    ' FOREIGN KEY(' + c1.name + ') REFERENCES dbo.' + 
    object_name(fk.referenced_object_id) + '(' + c2.name + ')'
from 
    sys.foreign_keys fk
inner join
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
inner join
    sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
inner join
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id

For these two queries, it's a two-step process:

• first execute the query that I show using C# and ADO.NET against your database
• this will produce an output which is a list of T-SQL commands (to DROP or re-create the FK relationships)
• take the output and in a second step, execute that output as a T-SQL command batch from your C#/ADO.NET application.

Limitation: right now, the script assumes and works only if you have single-column foreign keys; if you don't have that, you might need to tweak the scripts a bit.