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

sql-server tsql sql-server-2000
RetroCoder picture RetroCoder · Jan 23, 2012 · Viewed 81.5k times · Source

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.

Answer

Mikael Eriksson picture Mikael Eriksson · Jan 24, 2012

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

Related questions

Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward …

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More
How do I generate random number for each row in a TSQL Select?

I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row. SELECT table_name, RAND() magic_number FROM information_schema.tables I'd like to get an …

Read More