To drop all tables from a database

Question

Post reply

To drop all tables from a database

arthi.anan

Right there with Babe

Points: 798
More actions
December 13, 2012 at 10:55 pm

#286387

Hi,
I'm a beginner in sql server...
I need a query to drop all tables and stored procedures from a database so that i create a new tables in MSSQL 2008 without affecting other databases.
thanks in advance...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

arthi.anan (12/13/2012)
I need a query to drop all tables and stored procedures from a database

First of all , take backup of this database you never know what and when you would require previous data.

for dropping tables

select 'drop table ' + name from sys.objects where type = 'U'

for dropping Stored proc

select 'drop proc ' + name from sys.objects where type = 'P'

above queries will give you list of queries in below result pane but DOUBLE CHECK before executing it.

arthi.anan (12/13/2012)
so that i create a new tables in MSSQL 2008 without affecting other databases.

How and Why it will affect ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Probably be faster to drop the database and recreate it.

This will generate the drop statements for all tables and procedures, you can copy the output to a new window and then run it.

SELECT 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.tables

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.procedures

Won't take any foreign keys into account, if you have foreign keys you can run the drop table statements a few times until all the tables are gone

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arthi.anan Right there with Babe Points: 798 More actions · Answer 3

Thanks all....

My database name is Serv and I have 150 tables and 200 procedures...

How would i give it in this query...

Can u please make me clear

jeetsingh.cs SSCommitted Points: 1741 More actions · Answer 4

YOu can either come up with a procedure for this

where you pass database name as a parameter

or you can

modify the query

SELECT 'USE Serv DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.tables

SELECT ' USE Serv DROP PROCEDURE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.procedures

copy the data from a grid and can run on any database.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

arthi.anan (12/13/2012)
My database name is Serv and I have 150 tables and 200 procedures...
How would i give it in this query...
Can u please make me clear

you dont need to do any kind if special handling here just use query ( post by me or gail) in one window and copy the result in another window and run

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

arthi.anan (12/13/2012)
Thanks all....
My database name is Serv and I have 150 tables and 200 procedures...
How would i give it in this query...
Can u please make me clear

IF EXISTS (SELECT * FROM sys.databases WHERE name = N'Serv')

DROP DATABASE [Serv]

GO

CREATE DATABASE [Serv]

GO

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

_simon_ SSCrazy Points: 2329 More actions · Answer 7

A little side note - to drop all tables you can also use sp_msforeachtable procedure, like:

sp_msforeachtable 'drop table ?'

If your tables are connected with foreign keys, then you would have to run this procedure multiple times.

Sadly, there is no sp_msforeachprocedure procedure :hehe: