Get the temp table structure (e.g. generate sql script) and clear the temp table for the current instance

Question

How to get temp table structure and then delete temporary table. Is there sp_helptext for temporary tables? Finally, is it possible to delete the temporary table in the same session or query window?

Example:

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

Link

Answer 1

You need to use quotation marks around the temp table name, and you can delete the temporary table immediately 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 

Answer 2

So far I know there is no SP_HelpText for tables. Try the following:

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

Answer 3

I needed to be able to recreate the temporary table in a script, so I used this code to generate part of the columns 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) + ')', '') ELSE '' END + CASE WHEN c.IS_NULLABLE = 'YES' THEN ' NULL' ELSE '' END + ',' From tempdb.INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_NAME LIKE '#myTempTable%' 

I have not tested all sql data types, but this worked for int, float, datetime, money and bit.

In addition, ApexSQL Complete (free) has a nice feature that allows you to export grid results to the Insert Into statement. I used this to load this created temporary table in my script.

Answer 4

exec sp_columns table_name;

Example

exec sp_columns employees

Answer 5

 Select * From tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#yourtemp%' 

Answer 6

So that helped me. He created the columns of the table.

 Select Column_Name + ' [' + DATA_TYPE + ']' + case when Data_Type in ('numeric', 'varchar', 'char') then '(' + case when DATA_TYPE = 'numeric' then CAST(numeric_precision as varchar(3)) + ',' + CAST(numeric_scale as varchar(3)) when DATA_TYPE = 'varchar' then CAST(CHARACTER_MAXIMUM_LENGTH as varchar(3)) when DATA_TYPE = 'char' then CAST(CHARACTER_MAXIMUM_LENGTH as varchar(3)) end + ')' else '' end + ',' , * From tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#MEHTEMPTABLE%' 

All I needed to do then was copy these elements into the table declaration.

 Declare @MyTable Table ( --All columns here ) 

That would solve my problem, but I didn’t have enough time.