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

Truncating and deleting all tables in a database Expand / Collapse
Author
Message
Posted Friday, October 12, 2007 10:19 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, October 30, 2014 12:54 PM
Points: 115, Visits: 639
Comments posted to this topic are about the item Truncating and deleting all tables in a database


Post #410257
Posted Friday, April 11, 2008 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 9, 2010 2:37 PM
Points: 7, Visits: 26
I'm no SQL expert so correct me if I'm wrong, but...
This script gives me syntax errors because some of the table names in my database include spaces, hyphens and other strange characters.
So shouldn't we put brackets around the table names, i.e.
	if exists (select * from sysreferences where rkeyid = object_id(@tn))
/*table referenced by a FOREIGN KEY constraint */
set @sql = 'delete [' + @tn + ']'
else
set @sql = 'truncate table [' + @tn + ']'

Before someone says "You shouldn't put such characters in table names", the table names are not under my control.
Post #483823
Posted Wednesday, May 6, 2009 1:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 2, 2011 10:19 AM
Points: 320, Visits: 160
Hi,
I came across undocumented system stored procedure sp_MSForEachTable.
Try this to delete all data from all tables in a given database.
Please Test it.

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

EXEC sp_MSForEachTable ‘DELETE FROM ?’

EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

Thanks ,
Prem



Post #711480
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse