Drop Tables Using Pattern Matching

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

Transact-SQL (2000)

Drop Tables Using Pattern Matching

Author

Topic

RussB17
Starting Member

4 Posts

Posted - 2003-11-17 : 13:00:26

I would like to write SQL to drop multiple tables matching a patteren. It would be nice if this SQL statement would excute...But it will not: DROP TABLE tblTemp*
What are my options for getting the job done?
When we have power outages and client PCs shut down I end up with
some temp tables hanging around. I would like to run a Job at
4:00am to clean out these temp tables.

Any Thoughts anyone?
Thanks
RussB17

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 13:05:08

SELECT 'DROP TABLE ' + name + '
GO'
FROM sysobjects
WHERE name LIKE 'tblTemp%'

Run the query, copy the results into a new window, execute the results.

To schedule it, you'll need to loop through the result set and use dynamic sql. The query that I provided should give you a head start. For more information about dynamic sql, please search the forums for dynamic sql.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-17 : 13:11:06

quote:
Originally posted by RussB17

I would like to write SQL to drop multiple tables matching a patteren. It would be nice if this SQL statement would excute...But it will not: DROP TABLE tblTemp*
What are my options for getting the job done?
When we have power outages and client PCs shut down I end up with
some temp tables hanging around. I would like to run a Job at
4:00am to clean out these temp tables.

Any Thoughts anyone?
Thanks
RussB17

DANGER Will Robbinson!

User Creating "Temp" tables...as what owner? dbo?

What about #temp

Power outages? UPS? Clients go down? What about the server?

WARNING...WARNING...WARNING...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 13:13:25

If the app is creating these tables, you might want to reconsider your design. Have a look at temporary tables in SQL Server Books Online. Temporary tables when prefaced with # are really temporary. Yours are permanent until a DROP is done. Tell us about your design and we might be able to provide a better solution.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources