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 🙂
SQL Undercover 
I just wished that it worked when the @Query has a temp table in it 😦
LikeLiked by 1 person
Yeah that’s a really frustrating limitation. I was trying to make it work with a stored proc that includes a temp table but couldn’t find a way around it.
LikeLiked by 1 person
Select * into #temptable from sys.databases
LikeLike
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.
LikeLike