Empty the data from a large database??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Empty the data from a large database???

Author

Topic

mangalau
Starting Member

11 Posts

Posted - 2008-03-21 : 17:47:26

Hi i wanna delete all the records from an large database 200 -300 tables, because i want make some changes an start from scratch,but keep the structures of the database key , index etc, i tried to generate script but when i run to many errors , plz help 10x

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-21 : 17:54:22

Use 'truncate table'. What kind of error did you get by the way?

mangalau
Starting Member

11 Posts

Posted - 2008-03-21 : 18:06:42

I was thinking of that but on 300 tables , i tried to do a script or something who can do that, truncate on all the tables

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-21 : 18:39:37

You can't use truncate if you have foreign keys though.

You can script it by doing something like this:

SELECT 'TRUNCATE TABLE ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE <YourCriteria>

Switch to DELETE FROM if foreign keys exist.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mangalau
Starting Member

11 Posts

Posted - 2008-03-22 : 07:57:13

SELECT 'DELETE TABLE ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
DOESN'T DO ANYTHING HAS NO EFECT
DELETE TABLE XX
DELETE TABLE XX
BUT NOTHING HAPPENS

mfemenel
Professor Frink

1421 Posts

Posted - 2008-03-22 : 09:53:20

You could always script out the tables, drop them and recreate (provided you've covered your foreign key references)

Mike
"oh, that monkey is going to pay"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-22 : 12:20:00

Well my code scripts the queries for you. You then need to copy the output into a new window and then execute it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-23 : 21:59:31

If you do use DELETE, don't forget to truncate the log, as well.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

mangalau
Starting Member

11 Posts

Posted - 2008-03-24 : 06:00:20

i use delete but nothing happens and i have a 4gb log

mangalau
Starting Member

11 Posts

Posted - 2008-03-24 : 06:02:29

i remember i tried once to empty log file dont remember how empty_file something like that

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-24 : 06:06:49

Try this
Exec sp_msforeachtable 'Delete ?'
It is an undocumented system stroed proc.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-24 : 22:37:51

>> i use delete but nothing happens

What do you mean? Got any message? Double checked with 'sp_who2 active'?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources