To drop all tables from a database
-
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...
-
arthi.anan (12/13/2012)
I need a query to drop all tables and stored procedures from a databaseFirst 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;-) -
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.tablesSELECT 'DROP PROCEDURE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.proceduresWon'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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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
-
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.
-
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;-) -
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]GOCREATE DATABASE [Serv]GO -
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:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply