Removing data from a sqlserver database.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Removing data from a sqlserver database.

Author

Topic

bmistry
Starting Member

17 Posts

Posted - 2007-09-10 : 06:07:28

I would like to remove data from a sqlserver database but keep the structure the the db. How would I do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 06:10:30

Use the DELETE or TRUNCATE method.

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-10 : 06:15:31

Hi,

1. First take the Create table scripts for all tables( select triggers, foreignkeys, indexes etc in script options ).

2. then drop all tables ( u can generate drop table scripts and execute it )

3. run the create table scripts.

bmistry
Starting Member

17 Posts

Posted - 2007-09-10 : 06:22:21

Sorry I'm new to this where do I get the create script from?

SimonBowyer
Starting Member

3 Posts

Posted - 2007-09-10 : 06:30:03

Hi, this will sort you out, Simon

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

bmistry
Starting Member

17 Posts

Posted - 2007-09-10 : 06:35:02

In enterprise manager right click on the database and generate sql script, I take it the script can be created from there?

SimonBowyer
Starting Member

3 Posts

Posted - 2007-09-10 : 06:41:03

Fire up a query analyser session, make sure your in the db you want to empty ( worth putting a USE <Your database name> at the top) and run the script given.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 06:50:28

Make sure you have included ALL associated objects, such as logins, permissions, datatypes, triggers, udfs, sps, and so on............

E 12°55'05.25"
N 56°04'39.16"

bmistry
Starting Member

17 Posts

Posted - 2007-09-10 : 06:51:00

Thanks!!! that worked your a star!!!

SimonBowyer
Starting Member

3 Posts

Posted - 2007-09-10 : 07:05:27

don't thank me, i found that script on the web somewhere, a tip if your new to this game is keeping all the useful scripts you come across in an easy access location - I use Sharepoint WIKI library. Even if you don't it need at the time its worth copying them, adding some documenation for future reference.
Simon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources