July 7, 2018 at 1:41 pm
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.
July 8, 2018 at 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
;
July 8, 2018 at 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. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2018 at 9:28 am
Jeff Moden - Sunday, July 8, 2018 9:11 AMPretty 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.
😎
July 8, 2018 at 2:17 pm
Eirikur Eiriksson - Sunday, July 8, 2018 9:28 AMJeff Moden - Sunday, July 8, 2018 9:11 AMPretty 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
Change is inevitable... Change for the better is not.
July 9, 2018 at 3:26 pm
Eirikur Eiriksson - Sunday, July 8, 2018 1:49 AMWrote 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 databaseDECLARE @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 NULLEND
+ 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 NULLEND
+ 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''
ENDAS TABLE_DDL
;
Eirikur Eiriksson - Sunday, July 8, 2018 1:49 AMWrote 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 databaseDECLARE @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 NULLEND
+ 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 NULLEND
+ 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''
ENDAS TABLE_DDL
;
Eirikur, Thank very much for feedback. I go to work on this now.
July 9, 2018 at 3:33 pm
Eirikur, Thank very much for feedback. I go to work on this now!.
July 10, 2018 at 12:19 am
antoniop.silv - Monday, July 9, 2018 3:33 PMEirikur, 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.
July 10, 2018 at 6:17 am
Jeff Moden - Sunday, July 8, 2018 2:17 PMEirikur Eiriksson - Sunday, July 8, 2018 9:28 AMJeff Moden - Sunday, July 8, 2018 9:11 AMPretty 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
July 10, 2018 at 7:41 pm
Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AMantoniop.silv - Monday, July 9, 2018 3:33 PMEirikur, 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 😉
July 11, 2018 at 1:00 pm
Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AMantoniop.silv - Monday, July 9, 2018 3:33 PMEirikur, 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
July 11, 2018 at 1:00 pm
antoniop.silv - Wednesday, July 11, 2018 1:00 PMEirikur Eiriksson - Tuesday, July 10, 2018 12:19 AMantoniop.silv - Monday, July 9, 2018 3:33 PMEirikur, 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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply