how to delete delete records - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

how to delete delete records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-30 : 08:13:46

sonal writes "how to delete all records from all tables of particular database?"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-30 : 09:02:25

use msforeachtable
dynamic sql
delete construct
disabling constraints

or script out all table structure, permissions and objects (udf, sp's, etc) to another db and dump existing db.

search here for examples of each

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2005-11-30 : 11:06:22

Rather than using

DELETE FROM myTable

I'd use

TRUNCATE TABLE MyTable

as its not logged. As Andrew Says though you need to drop all the foreign Key constraints first Or you need to manually arrange the the Truncate Table Scripts in the correct order.

Personally I'd use the second approach which is to use DTS to copy the objects to an empty database, but not transfer the data.

--
Regards
Tony The DBA

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-30 : 11:35:38

"TRUNCATE" also required DBA/DBO access right....not every joesoap can run it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources