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

Picnic_table

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.


SET NOCOUNT ON

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

SELECT @ColumnList = STUFF((SELECT ',' + name + ' ' + system_type_name + ' ' +
CASE is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
+ CHAR(10)
FROM sys.dm_exec_describe_first_result_set(@Query, NULL, 0)
FOR XML PATH('')) ,1,1,'') 

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

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.

      Like

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: