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

Question

How do I get structure of temp table then delete temp table. Is there a sp_helptext for temp tables? Finally is it possible to then delete temp table in 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

Reference.

Using CTEs in Sql Server it seems to drop the temp table automatically: https://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 — RetroCoder, Jan 06 '18 at 00:05

score 121 · Accepted Answer · answered Jan 24 '12 at 06:50

121

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

answered Jan 24 '12 at 06:50

Mikael Eriksson

128,815
20
186
261

9

Might I add that this solution is a bit overkill on system resources. If just the table structure is needed, add a "WHERE 0=1" at the end of the select. – pkExec Aug 05 '14 at 12:38
Is there a reason this sp takes so long? – Sal Feb 13 '17 at 22:15

dajo · Answer 2 · 2020-11-10T15:28:55.413

21

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.

edited Nov 10 '20 at 15:28

answered Feb 21 '18 at 20:48

dajo

739
8
14

Good solution, if you'd add the max size you'd make it a great script! – Eli Apr 08 '19 at 21:28
Also want to ORDER BY the ordinal_position so the column defs come out in the same sequence as the original. – Paul Evans Nov 17 '19 at 05:14
2

@Eli, good suggestion. I edited the script for max size of character types – dajo Nov 18 '19 at 17:42
1

@PaulEvans, right. I edited the script to order the columns correctly – dajo Nov 18 '19 at 17:43
1

@RetroCoder may I suggest this be marked be marked as the accepted answer for it directly provides the requested output. – golfalot Mar 17 '20 at 22:49

score 11 · Answer 3 · edited Jan 24 '12 at 08:48

11

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

edited Jan 24 '12 at 08:48

musefan

45,726
20
123
171

answered Jan 24 '12 at 05:41

Geri Reshef

347
5
15

There certainly is sp_HelpText for tables, including temp tables. Please try `SELECT X INTO #TEMP FROM (VALUES (CAST (2 AS INT))) T (X); EXEC tempdb..sp_help '#TEMP'; ` – Reversed Engineer May 25 '18 at 13:02

score 4 · Answer 4 · answered Jan 18 '20 at 07:22

To Get structure of temp table

Many of us will use common methods like Keyboard Shortcut – ‘Alt+F1‘ or will use ‘SP_HELPTEXT‘ Command (so many other methods are also there) to view the Structure of Physical Table. As we all know, Viewing the Structure of Temp Table is not as common as Viewing the Structure of Physical Table. we are going to see, how to view the Structure of Temp Table easily in SQL Server. The below mentioning methods are applicable at both Azure SQL DB and On-Premises.

Demo SQL Script

IF OBJECT_ID('TempDB..#TempTable') IS NOT NULL
    DROP TABLE #TempTable;

SELECT 1 AS ID,'Arul' AS Names
INTO
#TempTable;

SELECT * FROM #TempTable;

METHOD 1 – Using SP_HELP

EXEC TempDB..SP_HELP #TempTable;

Note-

In the Table Structure, the Table Name shows something like ‘#TempTable__________________________________________________________________________________________________________0000000004CB’. Actually, the total length of each and every Temp Table name will be 128 . To handle the Same Temp Table name in Multiple Sessions differently, SQL Server will automatically add some underscores in between and alphanumeric’s at end.

METHOD 2 – Using SP_COLUMNS

EXEC TempDB..SP_COLUMNS '#TempTable';

METHOD 3 – Using System Tables like INFORMATION_SCHEMA.COLUMNS, SYS.COLUMNS, SYS.TABLES

SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (
SELECT NAME FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable')
);
GO

SELECT * FROM TempDB.SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO

SELECT * FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO

To Clear temp table for current instance

IF OBJECT_ID('TempDB..#TempTable') IS NOT NULL
    DROP TABLE #TempTable;

score 3 · Answer 5 · answered Sep 16 '13 at 05:33

3

exec sp_columns table_name;

example

exec sp_columns employees;

answered Sep 16 '13 at 05:33

Ravi Kumar

1,705
1
10
15

score 3 · Answer 6 · answered May 30 '16 at 06:00

3

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

answered May 30 '16 at 06:00

flyreaver

31
2

score 1 · Answer 7 · answered Apr 19 '19 at 08:53

So, this helped me. It created the Table columns.

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 then needed to do was copy these items into a Table Declaration

Declare @MyTable Table
(
--All columns here
)

That would've resolved my issue, but I was pressed for time

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

7 Answers7

Linked

Related