Home Dashboard Directory Help

Disable constraints like oracle, permiting truncate table by Thiagogvdasa


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 332015
Opened: 3/7/2008 3:30:11 AM
Access Restriction: Public


Today don´t hava one easy way, of clean a database
I have:
1) Generate script of foreign keys
2) Drop foreign key
3) truncate tables
4) recreate foreign keys

isn´t reliable and command delete is slowly.

Sign in to post a comment.
Posted by Microsoft on 1/25/2012 at 11:02 AM
Thank you for submitting this suggestion, but given its priority relative to the many other enhancements we are considering, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Thank you,
Eric Hanson
Program Manager
SQL Server Query Processing
Posted by Thiagogvdasa on 3/11/2008 at 3:23 PM

But tracate table doesn´t work on table, if the table has foreign key enabled or foreign key disabled.

I want to do, is I have one database on production with your users, and I have same database on development, but users on development its others. You Know?

What I can do?
Today I copy All data from production to development envirioment, but without drop user, because a dont want lost permissions on tables and procedures.
How I can extract this permissions? to reapply after restore, or How I can clean the database without generante one Big Log file and without lost my permissions?

Whats is the best pratice?
On oracle when I disable table constraints and I can truncate the table, but on sql server can´t do the same....

Posted by Microsoft on 3/11/2008 at 10:29 AM
If you want to create an empty database with equivalent structure to the existing one, you can script all the objects in it using management studio. See this URL:


Note that if all you want is an empty database to start fresh with, you should *not* script the statistics. But do script everything else.
Posted by Microsoft on 3/11/2008 at 10:20 AM
What exactly do you want to do? Do you want to remove all data from all tables in the database, but leave in place all the constraints? Or do you just want to remove data from a few tables that happen to be connected by foreign key constraints?
Posted by DB007 on 3/7/2008 at 1:50 PM
See workaround.
Sign in to post a workaround.
Posted by DB007 on 3/7/2008 at 1:56 PM
apologies the command is: sp_MSforeachtable (not sp_foreachtable)
Posted by DB007 on 3/7/2008 at 1:49 PM
It is possible to disable constraints, using the alter table check/nocheck constraints - refer to SQL BOL for more info.

You can also use the sp_foreachtable command (undocumented) - to disable all constraints on all tables:
sp_msforeachtable 'alter table ? nocheck constraint all'

You can use the undocumented sp_foreachtable command, to process all tables - for example run the truncate table as given below. Please be very carefull to run this on the correct database:

use [DBName]
sp_foreachtable 'truncate table ?'

Then re-enable all your constraints:
You can also use the sp_foreachtable command (undocumented) - to enable all constraints on all tables:

sp_msforeachtable 'alter table ? check constraint all'
File Name Submitted By Submitted On File Size  
truncate do BD.sql (restricted) 3/7/2008 -