Truncate All Tables

Question

Post reply

Truncate All Tables

Jorge Novo-@ETLDEVDBA

Ten Centuries

Points: 1265
More actions
April 25, 2008 at 9:15 pm

#206802

Comments posted to this topic are about the item Truncate All Tables

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

this works too:

drop database FOO

🙂

---------------------------------------
elsasoft.org

Eric J Peters SSC Rookie Points: 48 More actions · Answer 2

What's wrong with this:

EXEC sp_MSforeachtable @command1 = 'alter table ? nocheck constraint all'

EXEC sp_MSforeachtable @command1 = 'delete from ?'

EXEC sp_MSforeachtable @command1 = 'alter table ? check constraint all'

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 3

The proper syntax should be:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

where the ? is been replace for the table name

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 4

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?

DeepSQL-841864 SSC Enthusiast Points: 135 More actions · Answer 5

Hi Jorge

Its' good.

This works for me.

Where is the part 2?

Appreciated !!!

DeepSQL-841864 SSC Enthusiast Points: 135 More actions · Answer 6

Hi Jorge

Could 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 !

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 7

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 Name

declare @ChildColumn varchar(max)-- Child column Name

declare @MasterTable varchar (max) -- Master TAble

declare @MasterColumn varchar (max) -- Master Column reference

declare @sqlcmd varchar (max) -- Sql Command

declare @ConstraintName varchar(max) -- Constraint Name

declare ADD_Constraint cursor

fast_forward for

select distinct ConstraintName,ChildTable,ChildColumn,MasterTable,MasterColumn

from [CoDE].[dbo].t_FK_Xref order by ConstraintName

open ADD_Constraint

fetch next from ADD_Constraint

into @ConstraintName,

@ChildTable,

@ChildColumn,

@MasterTable,

@MasterColumn

while @@Fetch_Status = 0

begin

begin 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_Constraint

into @ConstraintName,

@ChildTable,

@ChildColumn,

@MasterTable,

@MasterColumn

end try

begin catch

print @sqlcmd+' ***** Error checkpoint '

fetch next from ADD_Constraint

into @ConstraintName,

@ChildTable,

@ChildColumn,

@MasterTable,

@MasterColumn

end catch

end

close ADD_Constraint

Deallocate ADD_Constraint

go

---------------5. Restore CHECK Constraints---------------

-- Now enable referential integrity again

--EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

print 'Constraints Restored'

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 8

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...

DeepSQL-841864 SSC Enthusiast Points: 135 More actions · Answer 9

Though it was little late

But i appreciate your help !

Thanks a lot Jorge !!

I'll let you know if i need more help

luckyHailong SSC Rookie Points: 36 More actions · Answer 10

/*I Do It As Follows:*/

DECLARE @ROWCOUNT INT

DECLARE @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=@@ROWCOUNT

WHILE @ROWCOUNT>0

BEGIN

SELECT @sSQL='TRUNCATE TABLE '+TableName FROM #DropTableNames WHERE TableID=@ROWCOUNT

SET @ROWCOUNT=@ROWCOUNT-1

EXEC SP_EXECUTESQL @sSQL

END

/*My English is poor So ...Here Is No Marker*/

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 11

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.

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 12

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 13

Also refer

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 14

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