Good post, here is another way, of course it uses the undocumented proc sp_MSForEachTable so it might not be allowed. I have used this in the past, I believe I saw this on Roman Rehak's site first but am not sure


-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

nice one, didn't cross my mind.

in fact I use slightly modified version that allows me to select tables I want to truncate, but it would probably work with this approach too.

And I didn't know you can truncate table with disabled constraints. To be honest I misunderstood BoL.

BoL: You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint.

There is another constraint for truncate - Indexed views. If you don't have large amount of data ordered delete should do the job.

Yeah also in order to do a TRUNCATE you need the following:

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

IMO, this and similar operation should be allowed only to DBA and DBA must have approval for it - even in development environments :-)

I think most that are concerned about actually securing data share the same opinion :-)

Hi Nikola,

I've tried to adapt your script for my case. I want to create a script creating several new tables (with foreign constraints, extended properties, etc.).

I used the part of generating dropping FK constraints. Can you also show how to drop all extended properties as I'm getting errors trying to create them if they exist?

Thanks.

Nikola,

Take a look at the comments at this thread

Relevant MSDN thread

Looks like you have to make this script more robust

I've tried to post the full script here, but it keeps erroring. I'll repost it on the msdn forum linked to above by Naomi.

Although we use script without problem, it seems it has to be modified to support schema bind objects. 2nd script (ordered delete statements) will not work for self referencing tables and in this case only way is to disable that FK.

Unfortunately I do not have time at the moment (at least next 2 weeks).

Here is my suggestion I posted in above mentioned msdn forum:

I would suggest you to use above script just to generate "execution" script. That means, instead of exec (@cmd) use Print @cmd.

Once you get it, you can test if it is correct, made necessary modifications (it is faster to add few modifications manually than spend weeks/months to make "generator" script smart enough). Once you are satisfied test generated script on pre-production (test) database (or at least make backup of production DB before you test it on live data - although I'll highly recommend you not to test anything on live DB) and once you are sure it's good enough you can run it in production DB.

In addition, it's easy to modify script to test and handle possible errors (use TRANSACTION and use TRY CATCH or simple IF @@ERROR with RAISERROR).

I rarely use immediate execution of generated batch of SQL statements, and never in production. There are a lot of reasons for it.

See also Delete tables' data and reset identities

Denis,

Your suggestion doesn't work, I just tried it and got an error.