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.
| Author |
Topic |
|
olily
Starting Member
37 Posts |
 Posted - 2002-05-10 : 02:41:39
|
| I have about 40 unused tables per day in tempdb in my sql server. I would like to write a sql script to automatically drop these tables but I do not know table names. It is generated by an application and the name format is 'mmddyyyyhhmmss'. The only thing that I can know which table that I want to drop is the table is created by user and not system. I'm using Sql Server 7.0 |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-05-10 : 04:32:38
|
| Temporary tables are stored in tempdb and are deleted automatically when no longer in use. So I guess your application creates 'permanent' tables in tempdb. This code will delete all user 'permanent' tables in tempdb, ie. those tables who name does not start with #. You may want to change the select statement to be more specific to your table names.DECLARE @sql nvarchar(200)DECLARE @tblname varchar(100)DECLARE temptbl_cur CURSOR FOR select name from tempdb..sysobjects where name not like '#%' and type = 'U' OPEN temptbl_curFETCH NEXT FROM temptbl_cur INTO @tblnameWHILE @@FETCH_STATUS = 0BEGIN SELECT @sql = 'DROP TABLE tempdb..' + @tblname exec sp_executesql @sql FETCH NEXT FROM temptbl_cur INTO @tblnameENDCLOSE temptbl_curDEALLOCATE temptbl_cur |
 |
|
|
|
|
|
How can I drop tables automatically?