It is possible to create a dynamic table from a result set ?

Question

It is possible to create a dynamic table from a result set ?

antoniop.silv

SSC-Addicted

Points: 435
More actions
July 7, 2018 at 1:41 pm

#372038

Hi guys,
I need help for store in a table #temp result set of a function PIVOT dynamics and join others data later, for example:
-- Declare variable
DECLARE    @query    VARCHAR(MAX)
-- Doing Dinamic Column
SET@query=''
SELECT@query=@query+'['+[a].[Column]+'], '
FROM
(SELECT DISTINCT
    CONVERT(VARCHAR(2),
    NomeTamanho) AS [Column]
FROM Tamanhos
)AS a
SET @query = LEFT(@query,len(@query)-1)
-- Doing query final with PIVOT
SET @query ='SELECT
        Descricao,
        CodProduto, '
        +@query
     +' FROM (SELECT P.Descricao, P.CodProduto, T.NomeTamanho, SUM(IP.Quant) QTDETOTAL '+
     '   FROM Produtos P, GradeProdutos GP, Tamanhos T, ItensPedidos IP '+
         ' WHERE P.CodProduto = GP.CodProduto '+
         ' AND P.CodProduto = IP.CodProduto '+
         ' AND GP.CodTamanho = T.CodTamanho '+
       ' AND IP.CodTamanho = T.CodTamanho '+
       ' AND SUBSTRING(P.CodProduto, 1,2 ) = ''CL'' '+
       ' AND SUBSTRING(P.CodProduto, 5,3 ) = ''053'' '+
       ' AND YEAR(P.DATA) >= 2012 '+
         ' GROUP BY P.CodProduto, P.Descricao, T.NomeTamanho '+') sq
        PIVOT (SUM(QTDETOTAL) FOR NomeTamanho IN('+@query+')) AS pt'
PRINT @query
EXEC (@query)
The variable @query return data I need store in #temp table for join others data later.
Thank you.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Wrote a function for this a while back, you might find it helpful.
😎
Details in the comments.
USE TEEST; GO SET NOCOUNT ON;

GO CREATE OR ALTER FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY ( @TABLE_NAME NVARCHAR(128) ,@SCHEMA_NAME NVARCHAR(128) ,@EXEC_SQL NVARCHAR(MAX) ,@PARAMETER_STR NVARCHAR(MAX) ,@IS_TABLE_VARIABLE BIT ,@IS_TEMPORARY_TABLE BIT ,@CREATE_INSERT_DML TINYINT ) RETURNS TABLE AS /****************************************************************************** ------------------------------------------------------------------------------- OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY ------------------------------------------------------------------------------- OBJECT VERSION: 1.7.43287 ------------------------------------------------------------------------------- OUTPUT: TABLE_DDL NVARCHAR(MAX) ------------------------------------------------------------------------------- PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS) ------------------------------------------------------------------------------- PARAMETERS: NAME Data type Desciption @TABLE_NAME NVARCHAR(128) The name of the target table

@SCHEMA_NAME NVARCHAR(128) Target schema name

@EXEC_SQL NVARCHAR(MAX) A valid sql statement that returns a result set. The user must have permissions to run the statement, otherwise the function will not return any output.

@PARAMETER_STR NVARCHAR(MAX) Parameter(s) for the sql statement in the same format as for the sp_executesql system procedure.

@IS_TABLE_VARIABLE BIT Generate table variable DDL 0 The function does not return a table variable DDL 1 The function returns a table variable DDL and prefixes the name passed in the @TABLE_NAME with "@"

@IS_TEMPORARY_TABLE BIT Generate temporary table DDL

@CREATE_INSERT_DML TINYINT Appends an insert statement to the output 0 No insert statement generated 1 Appends an insert statement to the output 2 Appends an insert statement to the output and adds the statement passed in the @EXEC_SQL parameter to the output. ------------------------------------------------------------------------------- AUTHOR: Eirikur Eiriksson http://www.eesql.com ------------------------------------------------------------------------------- This code is provided as is, without any warranty. It is free to use for any legitimate purposes, given that the whole and unedited comment section is included. ------------------------------------------------------------------------------- COPYRIGHT © 2012 - 2018 Eirikur Eiriksson ------------------------------------------------------------------------------- DESCRIPTION: ------------------------------------------------------------------------------- The function returns a DDL for any executable expression that returns a result set with named columns. Columns with no name (No column name) will be ignored and will not be present in the generated DDL.

The function can generate all three types of table DDL by setting the @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters:

DDL Type @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE ---------------------------------------------------------------- create table 0 0 create temporary table 0 1 declare table variable 1 0 NULL 1 1 ----------------------------------------------------------------

------------------------------------------------------------------------------- NOTE: ------------------------------------------------------------------------------- 1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1 then the function will return an empty result set (NULL).

2) The DDL will always be HEAP.

3) The column nullability is the only constraint in the DDL

4) The function will return an empty result set (NULL) if any query profiling options are enabled: a) STATISTICS PROFILE b) STATISTICS XML c) SHOWPLAN_ALL d) SHOWPLAN_TEXT e) SHOWPLAN_XML f) "Include Actual Execution Plan" (SSMS) ------------------------------------------------------------------------------- USAGE: ------------------------------------------------------------------------------- The function is an alternative and enhancement to SET FMTONLY which has been deprecated and OPENROWSET which requires enabeling the "Ad Hoc Distributed Queries" configuration.

------------------------------------------------------------------------------- VERSION HISTORY: ------------------------------------------------------------------------------- 1.0 Initial coding 1.1 Added table variable option 1.2 Added temporary table option 1.3 Output changed to NULL for invalid table type options 1.4 Minor changes in output formst Added usage examples to the comment section 1.5 Added the option of appending an insert statement to the output. 1.6 Changed the @CREATE_INSERT_DML parameter to TINYINT Added the option of appendin ghe statement passed in the @EXEC_SQL parameter to the output. 1.7 Fixed the usage examples. ------------------------------------------------------------------------------- EXAMPLES: ------------------------------------------------------------------------------- 1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic query: ------------------------------------------------------------------------------- DECLARE @TABLE_NAME NVARCHAR(128) = N'MY_NEW_TABLE'; DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo'; DECLARE @EXEC_SQL NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects'; DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 0; DECLARE @IS_TEMPORARY_TABLE BIT = 0; DECLARE @CREATE_INSERT_DML TINYINT = 0; SELECT X.TABLE_DDL FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME ,@SCHEMA_NAME ,@EXEC_SQL ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML) X

------------------------------------------------------------------------------- 2) Capture the output of a stored procedure into a temporary table (#TBL_SP_WHO) and select from the table; ------------------------------------------------------------------------------- DECLARE @TABLE_NAME NVARCHAR(128) = N'TBL_SP_WHO'; DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo'; DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who'; DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 0; DECLARE @IS_TEMPORARY_TABLE BIT = 1; DECLARE @CREATE_INSERT_DML TINYINT = 0; DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) = ( SELECT X.TABLE_DDL FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME ,@SCHEMA_NAME ,@EXEC_SQL ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML) X ) + N' INSERT INTO #' + @TABLE_NAME + N' ' + @EXEC_SQL + N'

SELECT * FROM #' + @TABLE_NAME + NCHAR(59);

EXEC (@EXEC_SP_WHO_TO_TABLE);

------------------------------------------------------------------------------- 3) Capture the result set of a stored procedure into a table variable (@SPWHO) and conditionally select from the table variable.

DECLARE @TABLE_NAME NVARCHAR(128) = N'SPWHO'; DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo'; DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who'; DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 1; DECLARE @IS_TEMPORARY_TABLE BIT = 0; DECLARE @CREATE_INSERT_DML TINYINT = 0; DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) = ( SELECT X.TABLE_DDL FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME ,@SCHEMA_NAME ,@EXEC_SQL ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML) X ) + N' INSERT INTO @' + @TABLE_NAME + N' ' + @EXEC_SQL + N'

SELECT * FROM @' + @TABLE_NAME + N' WHERE status = ''runnable'' ' + NCHAR(59);

EXEC (@EXEC_SP_WHO_TO_TABLE);

------------------------------------------------------------------------------- 4) Generate HEAP DDL for all tables in a database

DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 0; DECLARE @IS_TEMPORARY_TABLE BIT = 0; DECLARE @CREATE_INSERT_DML TINYINT = 0; DECLARE @NL NCHAR(2) = NCHAR(13) + NCHAR(10); DECLARE @QT NCHAR(2) = NCHAR(45) + NCHAR(45); SELECT CONCAT ( @QT ,QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name) ,@NL ,X.TABLE_DDL ,@NL ,@NL ) FROM sys.tables STAB CROSS APPLY dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY ( STAB.name ,OBJECT_SCHEMA_NAME(STAB.object_id) ,N'SELECT * FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name) + NCHAR(59) ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML ) X FOR XML PATH(''),TYPE; ******************************************************************************/ RETURN SELECT CASE -- Permanent table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME) + NCHAR(46) + QUOTENAME(@TABLE_NAME) + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

-- Temporary table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

-- Table variable WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN N'DECLARE ' + NCHAR(64) + (@TABLE_NAME) + N' TABLE ' + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

-- Other options are invalid and the result set is emptied by concatenating -- the output with NULL. ELSE NULL

END + NCHAR(13) + NCHAR(10) + NCHAR(32) + ( SELECT STUFF( ( SELECT NCHAR(44) + QUOTENAME(R.name) + N' ' + (R.system_type_name) + N' ' + CASE WHEN R.is_nullable = 0 THEN N'NOT NULL' ELSE N' NULL' END + NCHAR(13) + NCHAR(10) FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R WHERE R.is_hidden = 0 ORDER BY R.column_ordinal ASC FOR XML PATH(''),TYPE ).value('(./text())[1]','NVARCHAR(MAX)') ,1,1,N'') ) + NCHAR(41) + NCHAR(59) + CASE WHEN @CREATE_INSERT_DML > 0 THEN CASE -- Permanent table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN N'INSERT INTO ' + QUOTENAME(@SCHEMA_NAME) + NCHAR(46) + QUOTENAME(@TABLE_NAME) + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

-- Temporary table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN N'INSERT INTO ' + NCHAR(35) + @TABLE_NAME + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

-- Table variable WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN N'INSERT INTO ' + NCHAR(64) + (@TABLE_NAME) + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

-- Other options are invalid and the result set is emptied by concatenating -- the output with NULL. ELSE NULL

END + NCHAR(13) + NCHAR(10) + NCHAR(32) + ( SELECT STUFF( ( SELECT NCHAR(44) + QUOTENAME(R.name) + N' ' + NCHAR(13) + NCHAR(10) FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R WHERE R.is_hidden = 0 ORDER BY R.column_ordinal ASC FOR XML PATH(''),TYPE ).value('(./text())[1]','NVARCHAR(MAX)') ,1,1,N'') ) + NCHAR(41) ELSE N'' END

AS TABLE_DDL ;

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 2

Pretty cool, Eirikur. And, "Look Ma!!!!", it's fully documented! This thread is now a part of my briefcase!

You should write an article about this bad boy. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Jeff Moden - Sunday, July 8, 2018 9:11 AM
Pretty cool, Eirikur. And, "Look Ma!!!!", it's fully documented! This thread is now a part of my briefcase!
You should write an article about this bad boy. 😉

Thanks Jeff, it is a nifty little thing that has saved me many many hours of work. I'm happy to share and since you mentioned it, I will dig up the internal article I wrote at the time and publish it here.
😎

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 4

Eirikur Eiriksson - Sunday, July 8, 2018 9:28 AM
Jeff Moden - Sunday, July 8, 2018 9:11 AM
Pretty cool, Eirikur. And, "Look Ma!!!!", it's fully documented! This thread is now a part of my briefcase!
You should write an article about this bad boy. 😉
Thanks Jeff, it is a nifty little thing that has saved me many many hours of work. I'm happy to share and since you mentioned it, I will dig up the internal article I wrote at the time and publish it here.
😎

Sounds great. Thanks for the treat.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

antoniop.silv SSC-Addicted Points: 435 More actions · Answer 5

Eirikur Eiriksson - Sunday, July 8, 2018 1:49 AM
Wrote a function for this a while back, you might find it helpful.
😎
Details in the comments.
USE TEEST; GO SET NOCOUNT ON; GO CREATE OR ALTER FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY ( @TABLE_NAME NVARCHAR(128) ,@SCHEMA_NAME NVARCHAR(128) ,@EXEC_SQL NVARCHAR(MAX) ,@PARAMETER_STR NVARCHAR(MAX) ,@IS_TABLE_VARIABLE BIT ,@IS_TEMPORARY_TABLE BIT ,@CREATE_INSERT_DML TINYINT ) RETURNS TABLE AS /****************************************************************************** ------------------------------------------------------------------------------- OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY ------------------------------------------------------------------------------- OBJECT VERSION: 1.7.43287 ------------------------------------------------------------------------------- OUTPUT: TABLE_DDL NVARCHAR(MAX) ------------------------------------------------------------------------------- PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS) ------------------------------------------------------------------------------- PARAMETERS: NAME Data type Desciption @TABLE_NAME NVARCHAR(128) The name of the target table @SCHEMA_NAME NVARCHAR(128) Target schema name @EXEC_SQL NVARCHAR(MAX) A valid sql statement that returns a result set. The user must have permissions to run the statement, otherwise the function will not return any output. @PARAMETER_STR NVARCHAR(MAX) Parameter(s) for the sql statement in the same format as for the sp_executesql system procedure. @IS_TABLE_VARIABLE BIT Generate table variable DDL 0 The function does not return a table variable DDL 1 The function returns a table variable DDL and prefixes the name passed in the @TABLE_NAME with "@" @IS_TEMPORARY_TABLE BIT Generate temporary table DDL @CREATE_INSERT_DML TINYINT Appends an insert statement to the output 0 No insert statement generated 1 Appends an insert statement to the output 2 Appends an insert statement to the output and adds the statement passed in the @EXEC_SQL parameter to the output. ------------------------------------------------------------------------------- AUTHOR: Eirikur Eiriksson http://www.eesql.com ------------------------------------------------------------------------------- This code is provided as is, without any warranty. It is free to use for any legitimate purposes, given that the whole and unedited comment section is included. ------------------------------------------------------------------------------- COPYRIGHT Â© 2012 - 2018 Eirikur Eiriksson ------------------------------------------------------------------------------- DESCRIPTION: ------------------------------------------------------------------------------- The function returns a DDL for any executable expression that returns a result set with named columns. Columns with no name (No column name) will be ignored and will not be present in the generated DDL. The function can generate all three types of table DDL by setting the @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters: DDL Type @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE ---------------------------------------------------------------- create table 0 0 create temporary table 0 1 declare table variable 1 0 NULL 1 1 ---------------------------------------------------------------- ------------------------------------------------------------------------------- NOTE: ------------------------------------------------------------------------------- 1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1 then the function will return an empty result set (NULL). 2) The DDL will always be HEAP. 3) The column nullability is the only constraint in the DDL 4) The function will return an empty result set (NULL) if any query profiling options are enabled: a) STATISTICS PROFILE b) STATISTICS XML c) SHOWPLAN_ALL d) SHOWPLAN_TEXT e) SHOWPLAN_XML f) "Include Actual Execution Plan" (SSMS) ------------------------------------------------------------------------------- USAGE: ------------------------------------------------------------------------------- The function is an alternative and enhancement to SET FMTONLY which has been deprecated and OPENROWSET which requires enabeling the "Ad Hoc Distributed Queries" configuration. ------------------------------------------------------------------------------- VERSION HISTORY: ------------------------------------------------------------------------------- 1.0 Initial coding 1.1 Added table variable option 1.2 Added temporary table option 1.3 Output changed to NULL for invalid table type options 1.4 Minor changes in output formst Added usage examples to the comment section 1.5 Added the option of appending an insert statement to the output. 1.6 Changed the @CREATE_INSERT_DML parameter to TINYINT Added the option of appendin ghe statement passed in the @EXEC_SQL parameter to the output. 1.7 Fixed the usage examples. ------------------------------------------------------------------------------- EXAMPLES: ------------------------------------------------------------------------------- 1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic query: ------------------------------------------------------------------------------- DECLARE @TABLE_NAME NVARCHAR(128) = N'MY_NEW_TABLE'; DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo'; DECLARE @EXEC_SQL NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects'; DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 0; DECLARE @IS_TEMPORARY_TABLE BIT = 0; DECLARE @CREATE_INSERT_DML TINYINT = 0; SELECT X.TABLE_DDL FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME ,@SCHEMA_NAME ,@EXEC_SQL ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML) X ------------------------------------------------------------------------------- 2) Capture the output of a stored procedure into a temporary table (#TBL_SP_WHO) and select from the table; ------------------------------------------------------------------------------- DECLARE @TABLE_NAME NVARCHAR(128) = N'TBL_SP_WHO'; DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo'; DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who'; DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 0; DECLARE @IS_TEMPORARY_TABLE BIT = 1; DECLARE @CREATE_INSERT_DML TINYINT = 0; DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) = ( SELECT X.TABLE_DDL FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME ,@SCHEMA_NAME ,@EXEC_SQL ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML) X ) + N' INSERT INTO #' + @TABLE_NAME + N' ' + @EXEC_SQL + N' SELECT * FROM #' + @TABLE_NAME + NCHAR(59); EXEC (@EXEC_SP_WHO_TO_TABLE); ------------------------------------------------------------------------------- 3) Capture the result set of a stored procedure into a table variable (@SPWHO) and conditionally select from the table variable. DECLARE @TABLE_NAME NVARCHAR(128) = N'SPWHO'; DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo'; DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who'; DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 1; DECLARE @IS_TEMPORARY_TABLE BIT = 0; DECLARE @CREATE_INSERT_DML TINYINT = 0; DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) = ( SELECT X.TABLE_DDL FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME ,@SCHEMA_NAME ,@EXEC_SQL ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML) X ) + N' INSERT INTO @' + @TABLE_NAME + N' ' + @EXEC_SQL + N' SELECT * FROM @' + @TABLE_NAME + N' WHERE status = ''runnable'' ' + NCHAR(59); EXEC (@EXEC_SP_WHO_TO_TABLE); ------------------------------------------------------------------------------- 4) Generate HEAP DDL for all tables in a database DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL; DECLARE @IS_TABLE_VARIABLE BIT = 0; DECLARE @IS_TEMPORARY_TABLE BIT = 0; DECLARE @CREATE_INSERT_DML TINYINT = 0; DECLARE @NL NCHAR(2) = NCHAR(13) + NCHAR(10); DECLARE @QT NCHAR(2) = NCHAR(45) + NCHAR(45); SELECT CONCAT ( @QT ,QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name) ,@NL ,X.TABLE_DDL ,@NL ,@NL ) FROM sys.tables STAB CROSS APPLY dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY ( STAB.name ,OBJECT_SCHEMA_NAME(STAB.object_id) ,N'SELECT * FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name) + NCHAR(59) ,@PARAMETER_STR ,@IS_TABLE_VARIABLE ,@IS_TEMPORARY_TABLE ,@CREATE_INSERT_DML ) X FOR XML PATH(''),TYPE; ******************************************************************************/ RETURN SELECT CASE -- Permanent table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME) + NCHAR(46) + QUOTENAME(@TABLE_NAME) + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40) -- Temporary table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40) -- Table variable WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN N'DECLARE ' + NCHAR(64) + (@TABLE_NAME) + N' TABLE ' + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40) -- Other options are invalid and the result set is emptied by concatenating -- the output with NULL. ELSE NULL END + NCHAR(13) + NCHAR(10) + NCHAR(32) + ( SELECT STUFF( ( SELECT NCHAR(44) + QUOTENAME(R.name) + N' ' + (R.system_type_name) + N' ' + CASE WHEN R.is_nullable = 0 THEN N'NOT NULL' ELSE N' NULL' END + NCHAR(13) + NCHAR(10) FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R WHERE R.is_hidden = 0 ORDER BY R.column_ordinal ASC FOR XML PATH(''),TYPE ).value('(./text())[1]','NVARCHAR(MAX)') ,1,1,N'') ) + NCHAR(41) + NCHAR(59) + CASE WHEN @CREATE_INSERT_DML > 0 THEN CASE -- Permanent table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN N'INSERT INTO ' + QUOTENAME(@SCHEMA_NAME) + NCHAR(46) + QUOTENAME(@TABLE_NAME) + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40) -- Temporary table WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN N'INSERT INTO ' + NCHAR(35) + @TABLE_NAME + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40) -- Table variable WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN N'INSERT INTO ' + NCHAR(64) + (@TABLE_NAME) + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40) -- Other options are invalid and the result set is emptied by concatenating -- the output with NULL. ELSE NULL END + NCHAR(13) + NCHAR(10) + NCHAR(32) + ( SELECT STUFF( ( SELECT NCHAR(44) + QUOTENAME(R.name) + N' ' + NCHAR(13) + NCHAR(10) FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R WHERE R.is_hidden = 0 ORDER BY R.column_ordinal ASC FOR XML PATH(''),TYPE ).value('(./text())[1]','NVARCHAR(MAX)') ,1,1,N'') ) + NCHAR(41) ELSE N'' END AS TABLE_DDL ;

Eirikur, Thank very much for feedback. I go to work on this now.

antoniop.silv SSC-Addicted Points: 435 More actions · Answer 6

Eirikur, Thank very much for feedback. I go to work on this now!.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 7

antoniop.silv - Monday, July 9, 2018 3:33 PM
Eirikur, Thank very much for feedback. I go to work on this now!.

You are very welcome.
😎

Feel free to ping back if you have any further questions.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 8

Jeff Moden - Sunday, July 8, 2018 2:17 PM
Eirikur Eiriksson - Sunday, July 8, 2018 9:28 AM
Jeff Moden - Sunday, July 8, 2018 9:11 AM
Pretty cool, Eirikur. And, "Look Ma!!!!", it's fully documented! This thread is now a part of my briefcase!
You should write an article about this bad boy. 😉
Thanks Jeff, it is a nifty little thing that has saved me many many hours of work. I'm happy to share and since you mentioned it, I will dig up the internal article I wrote at the time and publish it here.
😎
Sounds great. Thanks for the treat.

I was thinking the same thing Jeff. And I stored the code in my toolbox as well. 🙂

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

antoniop.silv SSC-Addicted Points: 435 More actions · Answer 9

Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AM
antoniop.silv - Monday, July 9, 2018 3:33 PM
Eirikur, Thank very much for feedback. I go to work on this now!.
You are very welcome.
😎
Feel free to ping back if you have any further questions.

Thank you 😉

antoniop.silv SSC-Addicted Points: 435 More actions · Answer 10

Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AM
antoniop.silv - Monday, July 9, 2018 3:33 PM
Eirikur, Thank very much for feedback. I go to work on this now!.
You are very welcome.
😎
Feel free to ping back if you have any further questions.

Eirikur,

It's possible to access table #temp outside session, for example:
FOR TABLE = test

...
INSERT INTO #' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);

--After session closed
SELECT * FROM #test

antoniop.silv SSC-Addicted Points: 435 More actions · Answer 11

antoniop.silv - Wednesday, July 11, 2018 1:00 PM
Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AM
antoniop.silv - Monday, July 9, 2018 3:33 PM
Eirikur, Thank very much for feedback. I go to work on this now!.
You are very welcome.
😎
Feel free to ping back if you have any further questions.
Eirikur,
It's possible to access table #temp outside session, for example:
FOR TABLE = test
...
INSERT INTO #' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
--After session closed
SELECT * FROM #test

Thank you