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 = 0
,@IS_TEMPORARY_TABLE BIT = 0
,@CREATE_INSERT_DML TINYINT = 0
)
RETURNS TABLE
AS
/******************************************************************************
-------------------------------------------------------------------------------
OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
-------------------------------------------------------------------------------
OBJECT VERSION: 1.6.43221
-------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------
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 - 2016 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.
-------------------------------------------------------------------------------
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 @NL NCHAR(2) = NCHAR(13) + NCHAR(10); DECLARE @QT NCHAR(2) = NCHAR(45) + NCHAR(45); DECLARE @CREATE_INSERT_DML TINYINT = 0;
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
;