How can i drop all tables and...

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

Transact-SQL (2008)

How can i drop all tables and...

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-09-20 : 04:30:12

Hello,

I want to drop all tables and SP by single query

Regards,
ASIF

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-20 : 05:07:51

you could write a cursor to fetch names from sysobjects for all sps and tables and execute drop statements using dynamical sql.
Also, Can you let us know why would you want to delete all sps and tables from a db? I cant think of a reason.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 05:14:42

I don't know how to do it in a single query. But you can do the following
Get all the table & sp names from sys.objects and store those value in a temp table. Use while loop as follows:

CREATE TABLE #tmp
(id INT IDENTITY(1,1),
name VARCHAR(250),
tableOrSp VARCHAR(5)
)
INSERT INTO #tmp
SELECT name, CASE WHEN type = 'U' THEN 'TABLE'
WHEN type = 'p' THEN 'SP'
FROM sys.objects
WHERE type IN ('P','U')

DECLARE @cnt INT
DECLARE @maxcnt INT
SET @cnt=1
SELECT @maxcnt = COUNT(*) FROM #tmp

WHILE(@cnt <= @ maxcnt)
BEGIN
DECLARE @sql = VARCHAR(1000)
SELECT @name =name ,@type = tableOrSp FROM #tmp WHERE id=@cnt
IF(@type == 'TABLE')
SET @sql = 'DROP TABLE '+@name
ELSE
SET @sql = 'DROP PROCEDURE '+@name
EXEC(@sql)

SET @cnt =@cnt +1
END

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 05:26:07

Have a look at the system proc sp_foreachtable

Dont think there is an equiv for Sp's though.

Might be easier to create a new db and take over what you want!

Sachin.Nand

2937 Posts

Posted - 2010-09-20 : 05:26:17

quote:
Originally posted by asifbhura

Hello,

I want to drop all tables and SP by single query

Regards,
ASIF

Isn't this as simple as dropping the database?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources