Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Truncate All Tables Expand / Collapse
Author
Message
Posted Friday, April 25, 2008 9:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
Comments posted to this topic are about the item Truncate All Tables
Post #491023
Posted Friday, April 25, 2008 10:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589
this works too:

drop database FOO

:)


---------------------------------------
elsasoft.org
Post #491025
Posted Tuesday, June 3, 2008 5:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, June 3, 2008 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
The proper syntax should be:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
where the ? is been replace for the table name
Post #510797
Posted Tuesday, June 3, 2008 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
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
Posted Wednesday, August 13, 2008 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 13, 2008 11:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, August 18, 2008 6:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
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
Posted Monday, August 18, 2008 6:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
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
Posted Thursday, August 28, 2008 3:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 12»»

Permissions Expand / Collapse