WilberBeast

Sometimes it is really useful to work with temp tables and to create them up front rather than using ‘Select Into’, but they can be a pain create for a couple of reasons:

1. The temp table has loads of columns and you really don’t want to sit and type them all in.
2. You are not aware of the structure of the source data.

The following stored procedure takes the hassle away. Merely select some data (use Select Top(0) for speed) into a temp table that has the desired structure and then execute the stored procedure below to generate the SQL will create the temp table.

Anyway, here’s the code.

Create Procedure dbo.GenerateTempTableSQL
/******************************************************************************
Description:
    Given a temp table the sql required to create a new temp table of the same
    structure is generated.
    This is useful during development to select into a temp table and
    then generate the code that could be used to create the temp table.
In:
    @input_temp_table_name
        The name of the temp table to use as the template the generate the SQL
        from.
    @output_temp_table_name [optional: @input_temp_table_name]
        The name of the table that is used in the create table script. If
        left as null, then the @input_temp_table_name will be used.
    @database_collation [optional: current database collation]
        The collation to use for char based columns.
History:
    16-Oct-2015 S.Wilber
        Created
    09-Nov-2015 S.Wilber
        Updated to include # in temp table name if it had been ommitted.
    14-Jul-2016 S.Wilber
        Updated to work from temp tables instead of permanent tables
******************************************************************************/
    @input_temp_table_name sysname
   ,@output_temp_table_name sysname = null
   ,@database_collation sysname = null
As

-- declare constants
Declare     @reserved_words varchar(max) = '|ADD|ALL|ALTER|AND|ANY|AS|ASC|AUTHORIZATION|BACKUP|BEGIN|BETWEEN|BREAK|BROWSE|BULK|BY|CASCADE|CASE|CHECK|CHECKPOINT|CLOSE|CLUSTERED|COALESCE|COLLATE|COLUMN|COMMIT|COMPUTE|CONSTRAINT|CONTAINS|CONTAINSTABLE|CONTINUE|CONVERT|CREATE|CROSS|CURRENT|CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP|CURRENT_USER|CURSOR|DATABASE|DBCC|DEALLOCATE|DECLARE|DEFAULT|DELETE|DENY|DESC|DISK|DISTINCT|DISTRIBUTED|DOUBLE|DROP|DUMP|ELSE|END|ERRLVL|ESCAPE|EXCEPT|EXEC|EXECUTE|EXISTS|EXIT|EXTERNAL|FETCH|FILE|FILLFACTOR|FOR|FOREIGN|FREETEXT|FREETEXTTABLE|FROM|FULL|FUNCTION|GOTO|GRANT|GROUP|HAVING|HOLDLOCK|IDENTITY|IDENTITY_INSERT|IDENTITYCOL|IF|IN|INDEX|INNER|INSERT|INTERSECT|INTO|IS|JOIN|KEY|KILL|LEFT|LIKE|LINENO|LOAD|MERGE|NATIONAL|NOCHECK|NONCLUSTERED|NOT|NULL|NULLIF|OF|OFF|OFFSETS|ON|OPEN|OPENDATASOURCE|OPENQUERY|OPENROWSET|OPENXML|OPTION|OR|ORDER|OUTER|OVER|PERCENT|PIVOT|PLAN|PRECISION|PRIMARY|PRINT|PROC|PROCEDURE|PUBLIC|RAISERROR|READ|READTEXT|RECONFIGURE|REFERENCES|REPLICATION|RESTORE|RESTRICT|RETURN|REVERT|REVOKE|RIGHT|ROLLBACK|ROWCOUNT|ROWGUIDCOL|RULE|SAVE|SCHEMA|SECURITYAUDIT|SELECT|SEMANTICKEYPHRASETABLE|SEMANTICSIMILARITYDETAILSTABLE|SEMANTICSIMILARITYTABLE|SESSION_USER|SET|SETUSER|SHUTDOWN|SOME|STATISTICS|SYSTEM_USER|TABLE|TABLESAMPLE|TEXTSIZE|THEN|TO|TOP|TRAN|TRANSACTION|TRIGGER|TRUNCATE|TRY_CONVERT|TSEQUAL|UNION|UNIQUE|UNPIVOT|UPDATE|UPDATETEXT|USE|USER|VALUES|VARYING|VIEW|WAITFOR|WHEN|WHERE|WHILE|WITH|WITHIN GROUP|WRITETEXT|'

-- declare variables
Declare     @sql nvarchar(max)

-- table to store the columns
If Object_ID('tempdb..#temp') Is Not Null Drop Table #temp
Create Table #temp
(
    column_id int not null
   ,column_name nvarchar(130) null
   ,type_name nvarchar(130) not null
   ,max_length smallint not null
   ,scale tinyint not null
   ,[precision] tinyint not null
   ,is_nullable bit null
   ,collation_name sysname null
);

-- table to store the generated SQL
Declare     @output_sql table (line_id int identity(1, 1), sql nvarchar(max))
Declare     @object_id int

-- set the output name to be the input name if it is null or blank or whitespace
If NullIf(LTrim(RTrim(@output_temp_table_name)), '') Is Null Set @output_temp_table_name = @input_temp_table_name

-- set the collation if it's not set
If NullIf(LTrim(RTrim(@database_collation)), '') Is Null Set @database_collation = Cast(DatabasePropertyEx(db_name(), 'collation') As nvarchar(128))

-- add a # to the input temp table name if it does not already start with #
If Left(@input_temp_table_name, 1) != '#' Set @input_temp_table_name = '#' + @input_temp_table_name

-- add a # to the output temp table name if it does not already start with #
If Left(@output_temp_table_name, 1) != '#' Set @output_temp_table_name = '#' + @output_temp_table_name

-- get the object id
Select      @object_id = Object_ID('tempdb..' + @input_temp_table_name)

If @object_id Is Null
  Begin
    RaisError('''%s'' cannot be found.', 11, 1, @input_temp_table_name)
    Return
  End

-- generate the sql
Insert      @output_sql (sql)
Select      'If Object_ID(''tempdb..' + @output_temp_table_name + ''') Is Not Null Drop Table ' + @output_temp_table_name

Insert      @output_sql (sql)
Select      'Create Table ' + @output_temp_table_name

Insert      @output_sql (sql)
Select      '('

Insert      #temp (column_id, column_name, type_name, max_length, scale, precision, is_nullable, collation_name)
Select      c.column_id
           ,Case When CharIndex('|' + c.name + '|', @reserved_words) != 0 Then '[' + c.name + ']' Else c.name End As column_name
           ,Case When CharIndex('|' + t.name + '|', @reserved_words) != 0 Then '[' + t.name + ']' Else t.name End As type_name
           ,c.max_length
           ,c.scale
           ,c.precision
           ,c.is_nullable
           ,c.collation_name
From        tempdb.sys.objects o
Inner Join  tempdb.sys.schemas s On (s.schema_id = o.schema_id)
Inner Join  tempdb.sys.columns c On (o.object_id = c.object_id)
Inner Join  tempdb.sys.types t On (t.user_type_id = c.user_type_id)
Where       o.object_id = @object_id

Insert      @output_sql (sql)
Select      Case When column_id = 1 Then space(4) Else space(3) + ',' End
          + column_name + ' '
          + type_name
          + Case
                When Lower(type_name) In ('datetimeoffset', 'datetime2', 'time') Then
                    '(' + Cast(IsNull(c.scale, '') As varchar(10)) + ')'
                When Lower(type_name) In ('decimal', 'numeric') Then
                    '(' + Cast(IsNull(c.precision, '') As varchar(3)) + ', ' + Cast(IsNull(c.scale, '') As varchar(10)) + ')'
                When Lower(type_name) In ('varbinary', 'binary') Then
                    '('
                  + Case
                        When c.max_length = -1 Then
                            'Max'
                        Else
                            Cast(c.max_length As varchar(10))
                    End
                  + ')'
                When Lower(type_name) In ('char', 'varchar', 'nchar', 'nvarchar') Then
                    '('
                  + Case
                        When c.max_length = -1 Then
                            'Max'
                        When type_name In ('nchar', 'nvarchar') Then
                            Cast(c.max_length / 2 As varchar(10))
                        Else
                            Cast(IsNull(c.max_length, '') As varchar(10))
                    End
                  + ')'
                  + Case
                        When c.collation_name != @database_collation Then
                            ' Collate ' + c.collation_name
                        Else ''
                    End
                Else
                    ''
            End
          + Case
                When c.is_nullable = 0 Then
                    ' not null'
                Else
                    ' null'
            End
From        #temp c
Order By    column_id

Insert      @output_sql (sql)
Select      ');'

Select      sql
From        @output_sql
Order By    line_id