Auto Generate Create Table Script Based on SQL Server Query



By:   |   Read Comments   |   Related Tips: More > Functions – User Defined UDF







Problem

You have to create a SQL Server table to store the results from a query. At first you think
about looking at each column data type to create the table, but realize it will
be a tedious task. In this tip we look at a function you can use to generate a create
table script that has the correct data types for each column based on the source
columns.

Solution

We as SQL Server database professionals write queries each day and some circumstances we
need the query to store the data in a table so we can do further analysis or we
need to get the data from the query from different sources into one common table.
In such case, we can’t use a SELECT INTO statement to create the destination
table. This is especially important in case we are working inside a stored procedure
that uses a temporary table that needs to be populated with a SELECT statement twice.

For the purposes of this tip, I will use the query from my previous tip

Create SQL Server Disk Space Report for All Servers
as an example.

T-SQL Script to Generate a Table based on a Query

During the course of this tip we will go through the process of creating a scalar
function that takes a SELECT statement as a parameter and returns the CREATE TABLE
script for the query, so you can pass it as a parameter to an EXEC or sp_executesql
statement.

In order to achieve this, we will be using the Dynamic Management Function sys.dm_exec_describe_first_result_set.

The DMF sys.dm_exec_describe_first_result_set was introduced in SQL Server 2012.
This function has the following parameters: @tsql, @params and @browse_information_mode.
The table below has the descriptions of the parameters as found at the following
Microsoft help
link.






Parameter Description
tsql The Transact SQL script or batch.
params It provides a declaration string for parameters for the Transact-SQL
batch, which is similar to sp_executesql. Parameters may be nvarchar(n)
or nvarchar(max).
browse_information_mode Specifies if additional key columns and source table information are
returned. If set to 1, each query is analyzed as if it includes a FOR BROWSE
option on the query. Additional key columns and source table information
are returned.

  • If set to 0, no information is returned.
  • If set to 1, each query is analyzed as if it includes a FOR BROWSE
    option on the query. This will return base table names as the source
    column information.
  • If set to 2, each query is analyzed as if it would be used in preparing
    or executing a cursor. This will return view names as source column
    information.

Let’s run a SELECT statement using this function and using the query from
this tip

Create SQL Server Disk Space Report for All Servers
.  Note, I had to
make all single quotes into double single quotes since the query is passed as a
parameter.

USE AdventureWorks2012
GO
SELECT * FROM sys.dm_exec_describe_first_result_set ('
SELECT  @@SERVERNAME [Server] ,
        DB_NAME() [Database] ,
        MF.name [File Name] ,
        MF.type_desc [Type] ,
        MF.physical_name [Path] ,
        CAST(CAST(MF.size / 128.0 AS DECIMAL(15, 2)) AS VARCHAR(50)) + '' MB'' [File Size] ,
        CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 )) AS VARCHAR(50)) + '' MB'' [File Used Space] ,
        CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0) AS VARCHAR(50)) + '' MB'' [File Free Space] ,
        CAST(CONVERT(DECIMAL(10, 2), ( ( MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ) / ( MF.size / 128.0 ) ) * 100) AS VARCHAR(50)) + ''%'' [% Free File Space] ,
        IIF(MF.growth = 0, ''N/A'', 
           CASE WHEN MF.is_percent_growth = 1 THEN CAST(MF.growth AS VARCHAR(50)) + ''%'' 
           ELSE CAST(MF.growth / 128 AS VARCHAR(50)) + '' MB''
           END) [Autogrowth] ,
        VS.volume_mount_point ,
        CAST(CAST(VS.total_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50)) + '' GB'' [Total Volume Size] ,
        CAST(CAST(VS.available_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50)) + '' GB'' [Free Space] ,
        CAST(CAST(VS.available_bytes / CAST(VS.total_bytes AS DECIMAL(20, 2)) * 100 AS DECIMAL(20, 2)) AS VARCHAR(50)) + ''%'' [% Free]
FROM    sys.database_files MF
        CROSS APPLY sys.dm_os_volume_stats(DB_ID(''?''), MF.file_id) VS',NULL, null)

As you can see in the next screen capture, the output of this function is a table
that contains a detailed description of each column of the query that we provided
as the parameter.

Execution of sys.dm_exec_describe_first_result_set.

In the next table you will see a description of each output column. You can
see the full table at this

link
.

For our purposes we will only use the following columns: name, is_nullable, system_type_name,
collation_name and is_hidden to filter because we don’t want columns than
don’t appear in the result set.












































Column Description
is_hidden Specifies that the column is an extra column added for browsing and
informational purposes that does not actually appear in the result set.
column_ordinal Contains the ordinal position of the column in the result set. Position
of the first column will be specified as 1.
name Contains the name of the column if a name can be determined. If not,
will contain NULL.
is_nullable Contains the following values: Value 1 if column allows NULLs. Value
0 if the column does not allow NULLs. Value 1 if it cannot be determined
that the column allows NULLs.
system_type_id Contains the system_type_id of the column data type as specified in
sys.types. For CLR types, even though the system_type_name column will return
NULL, this column will return the value 240.
system_type_name Contains the name and arguments (such as length, precision, scale),
specified for the data type of the column. If data type is a user-defined
alias type, the underlying system type is specified here. If data type is
a CLR user-defined type, NULL is returned in this column.
max_length Maximum length (in bytes) of the column. -1 = Column data type is varchar(max),
nvarchar(max), varbinary(max), or xml. For text columns, the max_length
value will be 16 or the value set by sp_tableoption ‘text in row’.
precision Precision of the column if numeric-based. Otherwise returns 0.
scale Scale of column if numeric-based. Otherwise returns 0.
collation_name Name of the collation of the column if character-based. Otherwise returns
NULL.
user_type_id For CLR and alias types, contains the user_type_id of the data type
of the column as specified in sys.types. Otherwise is NULL.
user_type_database For CLR and alias types, contains the name of the database in which
the type is defined. Otherwise is NULL.
user_type_schema For CLR and alias types, contains the name of the schema in which the
type is defined. Otherwise is NULL.
user_type_name For CLR and alias types, contains the name of the type. Otherwise is
NULL.
assembly_qualified_type_name For CLR types, returns the name of the assembly and class defining the
type. Otherwise is NULL.
xml_collection_id Contains the xml_collection_id of the data type of the column as specified
in sys.columns. This column returns NULL if the type returned is not associated
with an XML schema collection.
xml_collection_database Contains the database in which the XML schema collection associated
with this type is defined. This column returns NULL if the type returned
is not associated with an XML schema collection.
xml_collection_schema Contains the schema in which the XML schema collection associated with
this type is defined. This column returns NULL if the type returned is not
associated with an XML schema collection.
xml_collection_name Contains the name of the XML schema collection associated with this
type. This column returns NULL if the type returned is not associated with
an XML schema collection.
is_xml_document Returns 1 if the returned data type is XML and that type is guaranteed
to be a complete XML document (including a root node), as opposed to an
XML fragment). Otherwise returns 0.
is_case_sensitive Returns 1 if the column is of a case-sensitive string type. Returns
0 if it is not.
is_fixed_length_clr_type Returns 1 if the column is of a fixed-length CLR type. Returns 0 if
it is not.
source_server Name of the originating server (if it originates from a remote server).
The name is given as it appears in sys.servers. Returns NULL if the column
originates on the local server or if it cannot be determined which server
it originates on. Is only populated if browsing information is requested.
source_database Name of the originating database returned by the column in this result.
Returns NULL if the database cannot be determined. Is only populated if
browsing information is requested.
source_schema Name of the originating schema returned by the column in this result.
Returns NULL if the schema cannot be determined. Is only populated if browsing
information is requested.
source_table Name of the originating table returned by the column in this result.
Returns NULL if the table cannot be determined. Is only populated if browsing
information is requested.
source_column Name of the originating column returned by the result column. Returns
NULL if the column cannot be determined. Is only populated if browsing information
is requested.
is_identity_column Returns 1 if the column is an identity column and 0 if not. Returns
NULL if it cannot be determined that the column is an identity column.
is_part_of_unique_key Returns 1 if the column is part of a unique index (including unique
and primary constraints) and 0 if it is not. Returns NULL if it cannot be
determined that the column is part of a unique index. Is only populated
if browsing information is requested.
is_updateable Returns 1 if the column is updateable and 0 if not. Returns NULL if
it cannot be determined that the column is updateable.
is_computed_column Returns 1 if the column is a computed column and 0 if not. Returns NULL
if it cannot be determined if the column is a computed column.
is_sparse_column_set Returns 1 if the column is a sparse column and 0 if not. Returns NULL
if it cannot be determined that the column is a part of a sparse column
set.
ordinal_in_order_by_list The position of this column is in ORDER BY list. Returns NULL if the
column does not appear in the ORDER BY list, or if the ORDER BY list cannot
be uniquely determined.
order_by_list_length The length of the ORDER BY list. NULL is returned if there is no ORDER
BY list or if the ORDER BY list cannot be uniquely determined. Note that
this value will be the same for all rows returned by sp_describe_first_result_set.
order_by_is_descending If the ordinal_in_order_by_list is not NULL, the order_by_is_descending
column reports the direction of the ORDER BY clause for this column. Otherwise
it reports NULL.
error_number Contains the error number returned by the function. If no error occurred,
the column will contain NULL.
error_severity Contains the severity returned by the function. If no error occurred,
the column will contain NULL.
error_state Contains the state message. returned by the function. If no error occurred,
the column will contain NULL.
error_message Contains the message returned by the function. If no error occurred,
the column will contain NULL.
error_type Contains an integer representing the error being returned. Maps to error_type_desc.
See the list under remarks.
error_type_desc Contains a short uppercase string representing the error being returned.
Maps to error_type. See the list under remarks.

SQL Server Function to Genereate Create Table Script

Here is the code of the function.

CREATE FUNCTION fn_Table_Structure (@InputSQL AS NVARCHAR(4000), @TableName AS NVARCHAR(128) = NULL) 
RETURNS NVARCHAR(4000)
AS
BEGIN

DECLARE @SQL AS NVARCHAR(4000)
DECLARE @name NVARCHAR(128)
DECLARE @is_nullable BIT 
DECLARE @system_type_name NVARCHAR(128)
DECLARE @collation_name NVARCHAR(128)
DECLARE @NewLine NVARCHAR(2) = CHAR(13) + CHAR(10) -- CRLF

DECLARE CUR_Table CURSOR LOCAL FAST_FORWARD
FOR
    SELECT  name ,
            is_nullable ,
            system_type_name ,
            collation_name
    FROM    sys.dm_exec_describe_first_result_set(@InputSQL, NULL, NULL)
    WHERE   is_hidden = 0
    ORDER BY column_ordinal ASC 

OPEN CUR_Table

FETCH NEXT FROM CUR_Table INTO @name, @is_nullable, @system_type_name,
    @collation_name

SET @SQL = 'CREATE TABLE [' + ISNULL(@TableName, 'TableName') + '] ('
    + @NewLine

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL += @NewLine + '[' + @name + ']' + ' ' + @system_type_name
            + CASE WHEN @collation_name IS NOT NULL
                   THEN '  COLLATE ' + @collation_name + ' '
                   ELSE ''
              END + CASE WHEN @is_nullable = 0 THEN ' NOT NULL '
                         ELSE ''
                    END + ',' 
        FETCH NEXT FROM CUR_Table INTO @name, @is_nullable, @system_type_name,
            @collation_name
    END

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + @NewLine + ')'

CLOSE CUR_Table
DEALLOCATE CUR_Table

RETURN @SQL
end 

The way this function works by declaring a cursor for each row returned by the
sys.dm_exec_describe_first_result_set Dynamic Management Function.

Parameters

This function receives the following parameters:





Parameter Description
InputSQL The Transact SQL script or batch that will be used to create the table
structure.
TableName The name of the table you want to generate from the create table script.

The function returns the create table statement based on the query passed as
the parameter. It includes the definition of nullable columns as well as the collation
for the string columns.

Here is an example of its use. Note, since the query is passed as a parameter,
I had to make each single quote into two single quotes for the original query.

DECLARE @sql AS NVARCHAR(4000)
SELECT @sql = dbo.fn_Table_Structure('
SELECT  @@SERVERNAME [Server] ,
        DB_NAME() [Database] ,
        MF.name [File Name] ,
        MF.type_desc [Type] ,
        MF.physical_name [Path] ,
        CAST(CAST(MF.size / 128.0 AS DECIMAL(15, 2)) AS VARCHAR(50)) + '' MB'' [File Size] ,
        CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 )) AS VARCHAR(50)) + '' MB'' [File Used Space] ,
        CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0) AS VARCHAR(50)) + '' MB'' [File Free Space] ,
        CAST(CONVERT(DECIMAL(10, 2), ( ( MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ) / ( MF.size / 128.0 ) ) * 100) AS VARCHAR(50)) + ''%'' [% Free File Space] ,
        IIF(MF.growth = 0, ''N/A'', 
           CASE WHEN MF.is_percent_growth = 1 THEN CAST(MF.growth AS VARCHAR(50)) + ''%'' 
           ELSE CAST(MF.growth / 128 AS VARCHAR(50)) + '' MB''
           END) [Autogrowth] ,
        VS.volume_mount_point ,
        CAST(CAST(VS.total_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50)) + '' GB'' [Total Volume Size] ,
        CAST(CAST(VS.available_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50)) + '' GB'' [Free Space] ,
        CAST(CAST(VS.available_bytes / CAST(VS.total_bytes AS DECIMAL(20, 2)) * 100 AS DECIMAL(20, 2)) AS VARCHAR(50)) + ''%'' [% Free]
FROM    sys.database_files MF
        CROSS APPLY sys.dm_os_volume_stats(DB_ID(), MF.file_id) VS
', 'Test' )
PRINT @sql
EXEC (@sql)

On the next image you can see the execution of the function. This will display
the results as well as create the table.

Here you can see the results of the function execution.

Next Steps



Last Update:


next webcast button

next tip button

About the author

MSSQLTips author Daniel Farina

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips


Leave a Reply

Your email address will not be published. Required fields are marked *

HashFlare