Get structure of temp table (like generate sql script) and clear temp table for current instance
Solution 1
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
Solution 2
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.
Solution 3
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');
Solution 4
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;
Solution 5
exec sp_columns table_name;
example
exec sp_columns employees;
RetroCoder
Development includes c#, asp, asp.net, mssql. Frameworks include angular, mvc, webapi. Other interests includes hiking, boxing, and swimming. I follow a number of other projects including apache, source forge projects and github projects. I am valiantly hunting down and unveiling the evil point farmers in all their hideous forms. To put them down you need a wooden stake and an endless supply of silver bullets.
Updated on July 05, 2022Comments
-
RetroCoder about 2 years
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
-
pkExec almost 10 yearsMight 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.
-
Sal over 7 yearsIs there a reason this sp takes so long?
-
Reversed Engineer about 6 yearsThere 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';
-
Eli about 5 yearsGood solution, if you'd add the max size you'd make it a great script!
-
Paul Evans over 4 yearsAlso want to ORDER BY the ordinal_position so the column defs come out in the same sequence as the original.
-
dajo over 4 years@Eli, good suggestion. I edited the script for max size of character types
-
dajo over 4 years@PaulEvans, right. I edited the script to order the columns correctly
-
golfalot over 4 years@RetroCoder may I suggest this be marked be marked as the accepted answer for it directly provides the requested output.