Truncate All Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jorge Novo » Truncate All Tables

18 posts, Page 1 of 2

1 2 »»

Truncate All Tables

Rate Topic

Display Mode

Topic Options

Author

Message

Jorge Novo-@ETLDEVDBA

Posted Friday, April 25, 2008 9:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

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

Post #491023

jezemine

Posted Friday, April 25, 2008 10:04 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 9:54 AM
Points: 470, Visits: 588

this works too:

drop database FOO

:)

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

Post #491025

Eric J. Peters-451285

Posted Tuesday, June 03, 2008 5:50 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 26, 2008 4:04 AM
Points: 12, Visits: 16

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'

Post #510567

Jorge Novo-@ETLDEVDBA

Posted Tuesday, June 03, 2008 9:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

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

Post #510797

Jorge Novo-@ETLDEVDBA

Posted Tuesday, June 03, 2008 9:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

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?

Post #510799

DeepSQL-841864

Posted Wednesday, August 13, 2008 8:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 3:08 PM
Points: 3, Visits: 14

Hi Jorge

Its' good.
This works for me.

Where is the part 2?

Appreciated !!!

Post #551957

DeepSQL-841864

Posted Wednesday, August 13, 2008 11:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 3:08 PM
Points: 3, Visits: 14

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 !

Post #552135

Jorge Novo-@ETLDEVDBA

Posted Monday, August 18, 2008 6:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

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'

Post #554259

Jorge Novo-@ETLDEVDBA

Posted Monday, August 18, 2008 6:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

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

Post #554260

DeepSQL-841864

Posted Thursday, August 28, 2008 3:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 3:08 PM
Points: 3, Visits: 14

Though it was little late

But i appreciate your help !

Thanks a lot Jorge !!

I'll let you know if i need more help

Post #560846

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions