SQLServerCentral / Discuss content posted by Jorge Novo / Article Discussions / Article Discussions by Author / Truncate All Tables / Latest Posts

RE: Truncate All Tables

Michael.Beeby — Thu, 02 Jun 2011 04:47:17 GMT

Just a thought, but if TRUNCATE then likely will reset SEEDS and there might be some cases where these might need to be kept? Maybe. Possibly. Especially if fed through to another system or report? Maybe.

RE: Truncate All Tables

Michael Valentine Jones — Thu, 28 Oct 2010 07:28:43 GMT

The script on the link below does a very good job of this without making any schema modifications, so it is a safer way to go.Truncate All Tables[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341[/url]

RE: Truncate All Tables

vipulsachan.kiet 1412 — Thu, 28 Oct 2010 07:01:24 GMT

i am vipul and i use this method to truncate all table from database. but constraint was not enabling by Exec sp_MSforeachtable 'alter table ? check constraint all'print 'all constraint enable'MESSAGE WILL APPEAR THAT- 'all constraint enable'BUT WHEN I CHECK THE DATABASE ALL CONSTRAINT DISABLESO WHAT I SHOULD DO ENABLE AGAIN ALL THE CONSTRAINTVIPUL SACHAN

RE: Truncate All Tables

Jorge Novo-@ETLDEVDBA — Mon, 17 Nov 2008 15:01:27 GMT

Very good information on the link. Like the Blog stated there are many ways to aproach and problem..Thanks for the input.

RE: Truncate All Tables

Madhivanan-208264 — Mon, 17 Nov 2008 05:36:20 GMT

Also refer[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx[/url]

RE: Truncate All Tables

Jorge Novo-@ETLDEVDBA — Mon, 17 Nov 2008 05:28:50 GMT

You can join the table with the names of tables also the statement.

RE: Truncate All Tables

Jorge Novo-@ETLDEVDBA — Mon, 17 Nov 2008 05:23:39 GMT

If not references are present on the tables:EXEC sp_MSForEachTable 'truncate TABLE ? ' will be enough to truncate all tables. the sp_MSForEachTable replace the ? for the actual table Name.

RE: Truncate All Tables

luckyHailong — Mon, 17 Nov 2008 02:38:36 GMT

/*I Do It As Follows:*/DECLARE @ROWCOUNT INTDECLARE @sSQL NVARCHAR(2000)CREATE TABLE #DropTableNames( TableName VARCHAR(30), TableID INT IDENTITY(1,1) not null)INSERT INTO #DropTableNames(TableName)SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type='BASE TABLE'SET @ROWCOUNT=@@ROWCOUNTWHILE @ROWCOUNT>0BEGIN SELECT @sSQL='TRUNCATE TABLE '+TableName FROM #DropTableNames WHERE TableID=@ROWCOUNT SET @ROWCOUNT=@ROWCOUNT-1 EXEC SP_EXECUTESQL @sSQLEND/*My English is poor So ...Here Is No Marker*/

RE: Truncate All Tables

DeepSQL-841864 — Thu, 28 Aug 2008 15:11:43 GMT

Though it was little lateBut i appreciate your help !Thanks a lot Jorge !!I'll let you know if i need more help

RE: Truncate All Tables

Jorge Novo-@ETLDEVDBA — Mon, 18 Aug 2008 06:42:17 GMT

I will gladly help you if you write about what are you trying to do. There are many uses for the script such as inserting into a bulk insert, deleting a child table, etc...

RE: Truncate All Tables

Jorge Novo-@ETLDEVDBA — Mon, 18 Aug 2008 06:40:28 GMT

Here it is:--------------4. Restore Relationships. ON delete NO action to be research ------------/* Restore relationships for delete set to no action; for update set to no action as on original reverse eng. model */ declare @ChildTable varchar (max) -- Child table Namedeclare @ChildColumn varchar(max)-- Child column Namedeclare @MasterTable varchar (max) -- Master TAbledeclare @MasterColumn varchar (max) -- Master Column referencedeclare @sqlcmd varchar (max) -- Sql Commanddeclare @ConstraintName varchar(max) -- Constraint Namedeclare ADD_Constraint cursorfast_forward forselect distinct ConstraintName,ChildTable,ChildColumn,MasterTable,MasterColumnfrom [CoDE].[dbo].t_FK_Xref order by ConstraintNameopen ADD_Constraintfetch next from ADD_Constraintinto @ConstraintName, @ChildTable, @ChildColumn, @MasterTable, @MasterColumn while @@Fetch_Status = 0beginbegin try select @sqlcmd = 'alter table '+@ChildTable+' with nocheck add constraint '+@ConstraintName+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'exec (@sqlcmd) fetch next from ADD_Constraintinto @ConstraintName, @ChildTable, @ChildColumn, @MasterTable, @MasterColumnend trybegin catchprint @sqlcmd+' ***** Error checkpoint 'fetch next from ADD_Constraintinto @ConstraintName, @ChildTable, @ChildColumn, @MasterTable, @MasterColumnend catchendclose ADD_ConstraintDeallocate ADD_Constraintgo---------------5. Restore CHECK Constraints--------------- -- Now enable referential integrity again--EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' print 'Constraints Restored'

RE: Truncate All Tables

DeepSQL-841864 — Wed, 13 Aug 2008 11:29:24 GMT

Hi JorgeCould you please the 2nd part of this too.I am working on similar thing, and i am sure your code 'll help me a lot.Thanks in advance !

RE: Truncate All Tables

DeepSQL-841864 — Wed, 13 Aug 2008 08:31:47 GMT

Hi JorgeIts' good.This works for me.Where is the part 2?Appreciated !!!

RE: Truncate All Tables

Jorge Novo-@ETLDEVDBA — Tue, 03 Jun 2008 09:50:08 GMT

What about if all you want is to give an empty shell to a developers groups or you want to do a bulk insert into multiple tables?

RE: Truncate All Tables

Jorge Novo-@ETLDEVDBA — Tue, 03 Jun 2008 09:47:26 GMT

The proper syntax should be:EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'where the ? is been replace for the table name

RE: Truncate All Tables

Eric J. Peters-451285 — Tue, 03 Jun 2008 05:50:28 GMT

What's wrong with this:[code]EXEC sp_MSforeachtable @command1 = 'alter table ? nocheck constraint all'EXEC sp_MSforeachtable @command1 = 'delete from ?'EXEC sp_MSforeachtable @command1 = 'alter table ? check constraint all'[/code]

RE: Truncate All Tables

jezemine — Fri, 25 Apr 2008 22:04:46 GMT

this works too: drop database FOO:)

Truncate All Tables

Jorge Novo-@ETLDEVDBA — Fri, 25 Apr 2008 21:15:24 GMT

Comments posted to this topic are about the item [B]Truncate All Tables[/B]