How to Get a List of All Current Temporary Tables in SQL Server

Is there a way to get a list of all current temporary tables in SQL Server?

Is this what you are after?

select * from tempdb..sysobjects
--for sql-server 2000 and later versions

select * from tempdb.sys.objects
--for sql-server 2005 and later versions

How to get a list of temporary tables created in a stored procedure?

I was able to construct a code to get the list of temporary tables and also set up the dynamic instruction to DROP each temporary table if it exists.

I leave the code and the links of the sources on which I was based.

CODE:

DECLARE @NameStoreProcedure AS VARCHAR(100) = 'Name_of_store_procedure' --Do not place the scheme

IF OBJECT_ID('tempdb..#Positions') IS NOT NULL
    DROP TABLE #Positions

IF OBJECT_ID('tempdb..#TemporalTableNames') IS NOT NULL
    DROP TABLE #TemporalTableNames

--Find all positions: http://dba.stackexchange.com/questions/41961/how-to-find-all-positions-of-a-string-within-another-string
DECLARE @term CHAR(20) = 'create'
DECLARE @string VARCHAR(MAX)

SELECT @string = OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE NAME = @NameStoreProcedure

SET @string += '.' --Add any data here (different from the one searched) to get the position of the last character

------------------------------------------------------------------------------------------------------------------------
--Range of numbers: http://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers-in-sql-server
DECLARE @min BIGINT
    , @max BIGINT

SELECT @Min = 1
    , @Max = len(@string)
------------------------------------------------------------------------------------------------------------------------

--Get positions of 'CREATE'
SELECT pos = Number - LEN(@term)
INTO #Positions
FROM (
    SELECT Number
        , Item = LTRIM(RTRIM(SUBSTRING(@string, Number, CHARINDEX(@term, @string + @term, Number) - Number)))
    FROM (
        SELECT TOP (@Max - @Min + 1) @Min - 1 + row_number() OVER (
                ORDER BY t1.number
                ) AS N
        FROM master..spt_values t1
        CROSS JOIN master..spt_values t2
        ) AS n(Number)
    WHERE Number > 1
        AND Number <= CONVERT(INT, LEN(@string))
        AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y

SELECT RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(substring(@string, pos - 1, CHARINDEX('(', @string, pos) - pos + 1), CHAR(9), ''), CHAR(13), ''), CHAR(10), ''))) AS NAME
INTO #TemporalTableNames
FROM #Positions
WHERE substring(@string, pos - 1, CHARINDEX('(', @string, pos) - pos + 1) LIKE '#%'

--List of temporary tables
SELECT NAME
FROM #TemporalTableNames

/*
--Dynamic Instruction for DROP instructios
    SELECT 'IF OBJECT_ID(''tempdb..' + NAME + ''') IS NOT NULL DROP TABLE ' + NAME
    FROM #TemporalTableNames
*/

Get list of columns in a temp table in SQL Server

Your were close. Just needed to point it to Tempdb.Sys.Columns

 Select * From  Tempdb.Sys.Columns Where Object_ID = Object_ID('tempdb..#TempTable')

Temp tables on the current connection

Assuming you don't name your #temp tables with three consecutive underscores, this should only pick up your #temp tables. It won't, however, pick up your table variables, nor can you change this code somehow to pick the tables on someone else's connection - this only works because OBJECT_ID('tempdb..#foo') can only return true for a table in your session.

SELECT 
  name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
  t.[object_id]
FROM tempdb.sys.tables AS t
WHERE t.name LIKE '#%[_][_][_]%'
AND t.[object_id] = 
  OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));

You might also be interested in space used by each of these tables (at least for the heap or clustered index), e.g.:

SELECT 
    name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
    t.[object_id], 
    p.used_page_count, 
    p.row_count
FROM tempdb.sys.tables AS t
INNER JOIN tempdb.sys.dm_db_partition_stats AS p
ON t.[object_id] = p.[object_id]
WHERE t.name LIKE '#%[_][_][_]%'
AND p.index_id IN (0,1)
AND t.[object_id] = 
    OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));

You could extend that to show total space for all indexes. I didn't bother aggregating per partition since these are #temp tables.

List down the column names in a Temporary Table in SQL Server

SELECT Obj.NAME
    ,Col.NAME
FROM tempdb.sys.objects Obj
INNER JOIN tempdb.sys.columns Col ON Obj.object_id = Col.object_id
WHERE Obj.NAME LIKE '#tmp%'

But please note that the local temporary table names will not be unique. We can have the same names from different sessions. So be careful with the query.

How to see temp table created by code in sql server?

After the code has finished and the session is closed, the temporary table will cease to exist. If you want to see it in SQL Server Management Studio (SSMS), you need to keep the code session open until the data has been reviewed in SSMS.

Per Technet:

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.

As an alternative, there's a C# code option here that selects the data from the temporary table into a code variable for review in the code ... (and if the code is to exist, you could possibly write it to a file or review by another means) -- see: https://stackoverflow.com/a/6748570/3063884

Find the column names of Temp table

SELECT *
FROM   tempdb.sys.columns
WHERE  object_id = Object_id('tempdb..#sometemptable');