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 tablefrom tMyTable  -- some table in your databaseexec 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.

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');