Delete tables - 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 2005 Forums

Transact-SQL (2005)

Delete tables

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-10-17 : 19:39:28

Hello,

Is there a way to delete all tables in a database?

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 19:43:14

Sure.

SELECT 'DROP TABLE' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

Just run copy the output of this query into a new window and execute it. If you have any foreign key constraints though, then you'll need to manually change the order so that all child tables are dropped before the parent.

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

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-10-17 : 19:47:54

Or just run it twice ...
Child tables gone on first ... parents on second ... :-)

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 23:45:26

You'd have to run it multiple times if you've got deep relationships, but yes that's typically what I do when I'm in a hurry.

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

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 00:05:47

Or just DROP and re-CREATE the database?

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 00:06:50

...Hmmm .. maybe that's a bad idea - perhaps you are wanting to keep users; or its a database provided to you and you don't have CREATE DATABASE permission?

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-10-18 : 06:37:09

Hi Kristen,

In this stage I can Drop the Database.
In fact that was what I tried before but I got an error. Something like:
"The database is being used"

I am using VS 2008 ... I have no idea why is being used besides the connection I have to it ... but if I remove the connection from it how can I delete it ...

I just started to use VS 2008 for SQL projects.
Until now I used SQL Management Studio.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 06:39:21

Your current connections can't be to the database you want to drop!

USE master
DROP MyDatabase

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 07:05:19

quote:
Originally posted by shapper

Hello,

Is there a way to delete all tables in a database?

Thanks,
Miguel

If you meant emptying all tables and not dropping, then truncate them
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-10-18 : 07:08:02

Hi Peso,

Thanks!

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-10-18 : 07:08:34

Madhivanan,

I really meant dropping the databases but that link will be useful to.

Thanks,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources