Seth Kramer (11/22/2010)
Okay but where does the numbering at the end of the underscores correspond to the spid that created it?Hypothetically, lets say I wanted to see what spid owned each copy of a local temp table. How would I do that? Also does it work for a global temp table.
Those last 12 characters do not correspond to the spid - they are sequentially assigned hexadecimal numbers based on the order that the temp tables are created. Run the following code (works best with results to text) to sequentially create the same temp table 100 times; you will see that the suffix appended to the table name is sequential.
SET NOCOUNT ON;
GO
if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test;
create table #test (RowID INT IDENTITY PRIMARY KEY CLUSTERED);
select name from tempdb.sys.tables where name like '#test%';
GO 100
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes