Truncating and deleting all tables in a database

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 hunchback » Truncating and deleting all tables in a...

Truncating and deleting all tables in a database

Rate Topic

Display Mode

Topic Options

Author

Message

hunchback

Posted Friday, October 12, 2007 10:19 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 19, 2012 1:42 PM
Points: 4, Visits: 32

Comments posted to this topic are about the item Truncating and deleting all tables in a database

Post #410257

Lars Huttar

Posted Friday, April 11, 2008 10:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 09, 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

prem

Posted Wednesday, May 06, 2009 1:18 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, March 02, 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 »

Permissions