A few years late to the question, but I needed something like this for some quick and dirty code generation. I believe as others have stated it is just easier to define the temp table up front, but this method should work for simple stored procedure queries or sql statments.
This will be a little convoluted, but it borrows from the contributors here as well as Paul White's solution from DBA Stack Exchange Get stored procedure result column-types. Again, to reiterate this approach & example is not designed for processes in a multi user environment. In this case the table definition is being set for a short time in a global temp table for reference by a code generation template process.
I haven't fully tested this so there may be caveats so you may want to go to the MSDN link in Paul White's answer. This applies to SQL 2012 and higher.
First use the stored procedure sp_describe_first_result_set which resembles Oracle's describe.
This will evaluate the first row of the first result set so if your stored procedure or statement returns multiple queries it will only describe the first result.
I created a stored proc to break down the tasks that returns a single field to select from to create the temp table definition.
CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
@sql NVARCHAR(4000)
,@table_name VARCHAR(100)
,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TempTableDefinition NVARCHAR(MAX)
DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)
DECLARE @ResultDefinition TABLE (
is_hidden bit NOT NULL
, column_ordinal int NOT NULL
, [name] sysname NULL
, is_nullable bit NOT NULL
, system_type_id int NOT NULL
, system_type_name nvarchar(256) NULL
, max_length smallint NOT NULL
, [precision] tinyint NOT NULL
, scale tinyint NOT NULL
, collation_name sysname NULL
, user_type_id int NULL
, user_type_database sysname NULL
, user_type_schema sysname NULL
, user_type_name sysname NULL
, assembly_qualified_type_name nvarchar(4000)
, xml_collection_id int NULL
, xml_collection_database sysname NULL
, xml_collection_schema sysname NULL
, xml_collection_name sysname NULL
, is_xml_document bit NOT NULL
, is_case_sensitive bit NOT NULL
, is_fixed_length_clr_type bit NOT NULL
, source_server sysname NULL
, source_database sysname NULL
, source_schema sysname NULL
, source_table sysname NULL
, source_column sysname NULL
, is_identity_column bit NULL
, is_part_of_unique_key bit NULL
, is_updateable bit NULL
, is_computed_column bit NULL
, is_sparse_column_set bit NULL
, ordinal_in_order_by_list smallint NULL
, order_by_is_descending smallint NULL
, order_by_list_length smallint NULL
, tds_type_id int NOT NULL
, tds_length int NOT NULL
, tds_collation_id int NULL
, tds_collation_sort_id tinyint NULL
)
INSERT @ResultDefinition
EXEC sp_describe_first_result_set @sql
;WITH STMT AS (
SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
UNION ALL
SELECT
CONCAT(
CASE column_ordinal
WHEN 1 THEN ' ' ELSE ' , ' END
, QUOTENAME([name]) , ' ', system_type_name
,CASE is_nullable
WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END
) AS TextVal
FROM @ResultDefinition WHERE is_hidden = 0
UNION ALL
SELECT N');' + @NewLine
)
SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT
SELECT @TableDefinition = @TempTableDefinition
END
The conundrum is that you need to use a global table, but you need to make it unique enough
so you can drop and create from it frequently without worrying about a collision.
In the example I used a Guid (FE264BF5_9C32_438F_8462_8A5DC8DEE49E) for the global variable replacing the hyphens with underscore
DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'
DECLARE @TableDef NVARCHAR(MAX)
DROP TABLE IF EXISTS
DROP TABLE IF EXISTS
EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
@sql, @GlobalTempTable, @TableDef OUTPUT
EXEC sp_executesql @TableDef
INSERT
EXEC sp_executesql @sql
SELECT *
INTO
FROM
SELECT * FROM
DROP TABLE IF EXISTS
DROP TABLE IF EXISTS
Again, I have only tested it with simple stored procedure queries and simple queries so your mileage may vary. Hope this helps someone.
