UNDERCOVER TOOLBOX: Generate a Temporary Table Definition to Match the Resultset of a Query


Have you ever needed to store the results of a complex query in a temp table?  How did you go about working out what the definition for that temp table should be, the columns and their data types?

It can be a bit of a pain, not to mention time consuming to have to go figuring out what all datatypes of the base tables are.

I got fed up with all that hunting around as well so as a quick blog I thought I’d share a little script that will take your query in a variable and print out a temp table definition for its result set.

It uses the fantastically useful DMV, sys.dm_exec_describe_first_result_set.

Just assign the value of @Query with the query that you’re interested in and @TempTableName with the name of the temp table that you want to create.


DECLARE @Query VARCHAR(MAX) = 'select * from sys.databases'
DECLARE @TempTableName VARCHAR(128) = '#temptable'

SELECT @ColumnList = STUFF((SELECT ',' + name + ' ' + system_type_name + ' ' +
+ CHAR(10)
FROM sys.dm_exec_describe_first_result_set(@Query, NULL, 0)
FOR XML PATH('')) ,1,1,'') 

PRINT 'CREATE TABLE ' + @TempTableName + '('
PRINT @ColumnList

I hope you find it useful 🙂

5 thoughts on “UNDERCOVER TOOLBOX: Generate a Temporary Table Definition to Match the Resultset of a Query

Add yours

    1. SELECT INTO is fine but there instances when you might need to explicitly declare a temp table. This little script also works with stored procs so can be handy if you’re writing an INSERT EXEC statement where you would need an explicit table definition.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: