Drop all user table - 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

Transact-SQL (2000)

Drop all user table

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2006-10-12 : 00:47:38

hi,

i want to delete all tables from a database. what is the command to delete all tables from database ones.

regards,
Irfan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 01:15:27

Be careful to take backup of the database before running below command !

exec sp_msforeachtable 'drop table ?'

If you don't want to undocumented command above, you will have to write cursor on your own and delete tables inside the cursor loop.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 02:08:22

Hopefully your current/active database is the one you want to delete all tables for.

Peter Larsson
Helsingborg, Sweden

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-12 : 05:25:37

You won't be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

-- First 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

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 05:34:51

If you run exec sp_msforeachtable 'drop table ?' x number of times, where x is the number of tables, the code will work.

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-12 : 07:12:43

quote:
i want to delete all tables from a database.

I read this as "I want to remove all tables from a database"

DROP DATABASE FOO

From BOL:

quote:
To use DROP DATABASE, the database context of the connection must be in the master database.

DROP DATABASE removes damaged databases marked as suspect and removes the specified database. Before dropping a database used in replication, first remove replication. Any database published for transactional replication, or published or subscribed to merge replication cannot be dropped. For more information, see Administering and Monitoring Replication. If a database is damaged and replication cannot first be removed, in most cases you still can drop the database by marking it as an offline database.

A dropped database can be re-created only by restoring a backup. You cannot drop a database currently in use (open for reading or writing by any user). When a database is dropped, the master database should be backed up.

System databases (msdb, master, model, tempdb) cannot be dropped.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 07:25:13

quote:
I read this as "I want to remove all tables from a database"

DROP DATABASE FOO

Not necessarily !

He may want to fool users by made them think there is some problem with the system not the database

Harsh Athalye
India.
"Nothing is Impossible"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-13 : 00:02:20

quote:
Originally posted by harsh_athalye
exec sp_msforeachtable 'drop table ?'

This won't work if you have foreign keys, unless you get lucky and all the fk tables get dropped before the pk tables.

You have to drop all the fks first if you want this to work.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 - http://www.elsasoft.org

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-13 : 01:29:27

if the error comes back as something like object table1 could not be found,then he's in BIG trouble

without a reason why he wants to drop all tables, +1 for drop database

quote:
Originally posted by harsh_athalye

Not necessarily !

He may want to fool users by made them think there is some problem with the system not the database

Harsh Athalye
India.
"Nothing is Impossible"

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 08:40:57

You can apply the logic used here

http://sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources