Dropping Many 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)

Dropping Many Tables

Author

Topic

Ditrex
Starting Member

14 Posts

Posted - 2007-08-07 : 10:58:52

I'm trying to drop a large amount of tables from a database called HISTORY. I'm wondering how to do this. This is what I've tried so far:

SELECT drop table name
FROM sys.tables
WHERE name like '200720_%'

This gives me an error. When I use sys.tables it shows me all the tables in the HISTORY database, so I thought about using it in a select type statement.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-08-07 : 11:22:48

You have the right idea :

SELECT 'drop table ' + name
FROM sys.tables
WHERE name like '200720_%'

Ditrex
Starting Member

14 Posts

Posted - 2007-08-07 : 11:29:47

I tried it, but it didn't really work. The code completed but it didn't delete the tables. It showed a result set which had the words drop table + the table name.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-08-07 : 11:39:54

quote:
Originally posted by Ditrex

I tried it, but it didn't really work. The code completed but it didn't delete the tables. It showed a result set which had the words drop table + the table name.

Yep, it's jsut returning a result set. You can copy that result set, paste it into another window, and execute it. Then the tables will be deleted.

Your other option is to create a cursor, and then loop through each record an delete it like that. Your cursor select would look like :

SELECT name
FROM sys.tables
WHERE name like '200720_%'

Then within the cursor you would have something like :

execute('drop table ' + @tablename)

Ditrex
Starting Member

14 Posts

Posted - 2007-08-07 : 11:51:13

Sorry, I'm new to cursors, how would you set it up?

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-08-07 : 12:41:13

Dude, you need to use books online. It's a very basic operation. Also, if you just need to execute this once, just take the delete statements from the result set and execute them.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-07 : 13:58:47

You can do it this way in SQL Server 2005


declare @x nvarchar(max)

select
	@x = isnull(@x,'')+
'
drop table '+name+';
'
from
	sys.tables
where
	name like '200720_%'

print @x

execute ( @x )

CODO ERGO SUM

Ditrex
Starting Member

14 Posts

Posted - 2007-08-07 : 16:20:52

Thanks! That worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources