how to delete a global temp table
-
Hi,
we have a global temp table in one of our databases that was created long time ago.
I read that the global temp table will be deleted when all the connections referring to it are deleted. since the table was created long time ago I don't think we have any connections that are still referring to that table. But when I am trying to delete that table it is saying "table doesn't exist in the system catalog". How could I delete that table?
Thanks
sridhar.
-
Hello Sridhar,
If the message says that the table doesn't exist then it might have been deleted. Otherwise, check the sysobjects in tempdb database as all the temporary objects will be created in that database.
Hope you will find some lead.
Thanks
Lucky -
If you get really desperate and are able to, you could always restart the SQL server services. Each time you restart SQL, tempdb gets recreated. Unless you have a process somewhere that is creating this global temp table without your knowledge, this is a quick, dirty, and brute force fix.
-
we have created that table probably 2 or 3 years back. And we have restarted our sql server several times after that. when i try to drop that table using t-sql command it says object doesn't exist in sysobjects table. but i could see that table in the query analyzer object browser and in Enterprise manager also. Is there a way to delete that table forcibly even if it has connections referrring to that table.
Please let me know.
Thanks,
sridhar.
-
Yes to all, but there may be simpler solutions.
Can you please run the following SQL statements and post the results?
1.
USE MASTER
SELECT OBJECT_ID('tempdb..##TempTableName')
2. Run the below and see if anything is fishy.
USE MASTER
EXEC sp_help '##TempTableName'
3. How many records are coming back in response to this query?
SELECT *
FROM tempdb..sysobjects a
INNER JOIN tempdb..syscolumns b
ON a.id = b.id
WHERE a.id = object_id('tempdb..##TempTableName')
4. What's the result of
SELECT COUNT(*) FROM ##TempTableName
Most likely, the tempdb got corrupt a while back for some reason and the table is still in the system tables in tempdb (assuming that the table is indeed the one created long ago and was not recreated recently again). You can clean up the system tables (not recommended, but possible) by running
USE MASTER
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
-
Hi,
I did all the steps that you have mentioned. However it didn't delete the global temp table. Please let me know what I might be missing.
Thanks,
sridhar.
-
Can you post the exact statement you use to delete global temp table?
_____________
Code for TallyGenerator -
Sridhar,
The steps should NOT HAVE deleted anything. They were supposed to return results. Please post these results so I can better see what's going on. That was the reason for posting the above steps
-
They have returned nothing.
when I ran step1 it gave NULL
when I ran step2 it gave The object '##temptablename' does not exist in database 'master' or is invalid for this operation.
when I ran step3 it gave empty resultset
when I ran step4 it gave invalid object name
-
You did replace ##temptablename with the name of the table that you see in your database, right? For Example ##MyTable?
-
yes I did.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply