Dropping Tables - Optimize Query

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)

Dropping Tables - Optimize Query

Author

Topic

OON
Starting Member

22 Posts

Posted - 2010-10-28 : 11:35:30

Hello,

i have the following script to drop all tables. is there a way to optimize this query as it takes too long when executed. also, how do i make sure that it only drops tables in my schema and not just all tables in the database.

Thanks

QUERY -

SELECT name INTO #tables from sys.objects where type = 'U'

while (SELECT count(1) FROM #tables) >= 0

begin

declare @sql varchar(max)

declare @tbl varchar(255)

SELECT top 1 @tbl = name FROM #tables

SET @sql = 'drop table ' + @tbl

exec(@sql)

DELETE FROM #tables where name = @tbl

end

DROP TABLE #tables;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 11:53:10

I don't know how a drop table should get faster...

And for the second question:
It is possible to join sys.schemas...

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-28 : 12:01:42

Try using the INFORMATION_SCHEMA.Tables view. That exposes the schema as well as teh table name so you can delete the proper table.

Kristen
Test

22859 Posts

Posted - 2010-10-28 : 12:01:47

Probably no faster, but I prefer this method:


SELECT IDENTITY(int, 1, 1) AS T_ID, name INTO #tables ...

DECLARE @intLoop int, @intRowCount int

SELECT @intRowCount = 1, -- Force first iteration
       @intLoop = 1

WHILE @intRowCount >= 1
BEING
    SELECT TOP 1
           @intLoop = T_ID+1,
           @tbl = name
    FROM #tables
    WHERE T_ID >= @intLoop
    ORDER BY T_ID
    SELECT @intRowCount = @@ROWCOUNT

... processing ...

END

OON
Starting Member

22 Posts

Posted - 2010-10-28 : 12:47:45

Thanks all.

@ Kristen - what is the code you sent supposed to be doing?

i ask because i dont have a test environment so i want to understand what the code is doing.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-28 : 13:37:05

It's doing the same thing your loop does, just differently.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-28 : 14:13:49

Also, the reason your query is taking a long time is because you have in infinite loop.

Try changing:

--FROM
while (SELECT count(1) FROM #tables) >= 0

--TO
while (SELECT count(1) FROM #tables) > 0

OON
Starting Member

22 Posts

Posted - 2010-10-29 : 13:25:30

Ok thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-29 : 13:35:43

Did you backup your database first?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

OON
Starting Member

22 Posts

Posted - 2010-10-29 : 13:46:19

Brett, no i didn't. why do u ask that?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-29 : 16:04:09

in case u need to get anything back/drop something u didnt intend to

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources