April 5, 2013 at 10:23 am
Hello - Sometimes I create temp tables as part of running ad hoc queries.
How long does SS keep temp tables? I seem to remember that temp table references are stored in sys.objects. How can I query sys.objects (or other appropriate table) to get a list of the temp tables I've created?
I'm assuming I can delete my temp tables from the same table in which my temp table references are stored by using a standard delete statement?
April 5, 2013 at 10:27 am
Did you try?
SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'yourTableName'
April 5, 2013 at 10:50 am
Are you talking about tables like this #MyTempTable?
April 5, 2013 at 10:53 am
I see objects in the table with a create date going back several years. When you create a temp table does SS store this table indefinitely?
Also, I seem to remember that it was possible to identify temp tables that were created by my client ID. Can you please refresh my memory on how to do this?
April 5, 2013 at 11:13 am
sqlguy-736318 (4/5/2013)
I see objects in the table with a create date going back several years. When you create a temp table does SS store this table indefinitely?Also, I seem to remember that it was possible to identify temp tables that were created by my client ID. Can you please refresh my memory on how to do this?
Again, are you talking about tables like this #MyTempTable?
April 5, 2013 at 1:57 pm
In SQL Server, temporary tables start with # or ##, such as #table or ##table.
IIRC:
SQL will automatically destroy # tables when the connection ends.
For ## tables, under SQL 2005 and later, SQL will destroy the table when the last connection using it ends.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 5, 2013 at 2:23 pm
Don't forget that temp table are in fact found in sys.objects BUT not in the current database. They will always be created in tempdb.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 5, 2013 at 10:45 pm
TenC - Thanks for that tip. What you're describing seems logical.
Yes - the temp tables I'm using in my SQL are in the format #MyTempTable. So hopefully SS destroys these tables when the connection ends.
Can anyone else here please confirm that this is the standard SS behavior?
April 6, 2013 at 6:26 am
From Books Online:
Temporary Tables
There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2013 at 6:27 am
SQL ORACLE (4/5/2013)
Did you try?SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'yourTableName'
sys.sysobjects is deprecated, included only for backward compat with SQL 2000 and should not be used. The replacement is sys.objects or, for user tables, sys.tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply