Get structure of temp table (like generate sql script) and clear temp table for current instance

You need to use quotes around the temp table name and you can delete the temp table directly after using drop table ....

select *
into #myTempTable  -- creates a new temp table
from tMyTable  -- some table in your database

exec tempdb..sp_help '#myTempTable'

drop table #myTempTable

I needed to be able to recreate a temp table in a script, so I used this code generate the columns part of the CREATE TABLE statement:

SELECT char(9) + '[' + c.column_name + '] ' + c.data_type 
   + CASE 
        WHEN c.data_type IN ('decimal')
            THEN isnull('(' + convert(varchar, c.numeric_precision) + ', ' + convert(varchar, c.numeric_scale) + ')', '') 
        WHEN c.data_type IN ('varchar', 'nvarchar', 'char', 'nchar')
            THEN isnull('(' 
                + CASE WHEN c.character_maximum_length = -1
                    THEN 'max'
                    ELSE convert(varchar, c.character_maximum_length) 
                  END + ')', '')
        ELSE '' END
   + CASE WHEN c.IS_NULLABLE = 'YES' THEN ' NULL' ELSE '' END
   + ','
FROM tempdb.INFORMATION_SCHEMA.COLUMNS c 
WHERE TABLE_NAME LIKE '#myTempTable%' 
ORDER BY c.ordinal_position

I didn't test for all sql datatypes, but this worked for int, float, datetime, money, and bit.

Also - ApexSQL Complete (free) has a nice feature where you can export grid results into an Insert Into statement. I used this to load this created temp table in my script. ApexSQL Copy Results As Insert into statement


As long as I know there is no SP_HelpText for tables. Try this:

Select * From tempdb.sys.columns Where object_id=OBJECT_ID('tempdb.dbo.#myTempTable');