Disable constraints like oracle, permiting truncate table - by Thiagogvdasa

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 332015 Comments
Status Closed Workarounds
Type Suggestion Repros 0
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.