Create a table based on a temporary table schema

First of all, this is not really a good practice to do in your production environment but this is a script i created which allows you to create a composite table from a temporary table

This has quite a few limitations but generally gives you the basic structure of the table you want without keys, indexes, null contraints etc
This has been created to work with SQL Server 2012, if you need to make it work for older versions you will need to update the CONCAT function to use the old string concatenation
Replace your table names with the ones in the script

Heres the script

/*
 Intialise variables and set table names you want to create a table from
*/
DECLARE @columns VARCHAR(max)
DECLARE @createsql nVARCHAR(max)
DECLARE @tempTableName sysname
DECLARE @newTableName sysname
DECLARE @columnIterator int
DECLARE @columnName sysname
DECLARE @columnType VARCHAR(13)

SET @tempTableName = '#temp'
SET @newTableName = 'MYNEWTABLE'


/*
 Check the temp table exists
*/
IF OBJECT_ID(CONCAT('tempdb.dbo.',@tempTableName)) IS NULL
BEGIN
 PRINT 'Temp table does not exist'
 RETURN
END
 
/*
 Table variable to hold the columns
*/
DECLARE @cols TABLE ([TABLE_QUALIFIER] sysname, [TABLE_OWNER] sysname, [TABLE_NAME] sysname, [COLUMN_NAME] sysname, [DATA_TYPE] smallint, 
[TYPE_NAME] varchar(13), [PRECISION] int, [LENGTH] int, [SCALE] smallint, [RADIX] smallint, [NULLABLE] smallint, 
[REMARKS] varchar(254), [COLUMN_DEF] nvarchar(4000), [SQL_DATA_TYPE] smallint, [SQL_DATETIME_SUB] smallint, 
[CHAR_OCTET_LENGTH] int, [ORDINAL_POSITION] int, [IS_NULLABLE] varchar(254), [SS_DATA_TYPE] TINYINT
)

INSERT INTO @cols
EXEC tempdb..sp_columns @table_name = @tempTableName


/*
 build the create table statement using the columns we know about
*/
SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
from @cols
ORDER BY ORDINAL_POSITION

WHILE 1=1
BEGIN

 SET @columns = CONCAT(@columns,' ',@columnName,' ', @columnType, ', ')

 SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
 from @cols
 WHERE @columnIterator < ORDINAL_POSITION
 ORDER BY ORDINAL_POSITION

 IF @@ROWCOUNT = 0
  BREAK

END

SET @columns = left(@columns,LEN(@columns)-1)
SET @createsql = CONCAT('CREATE TABLE ', @newTableName, '( ',@columns,') ')

/*
 Create the table
*/
EXEC sp_executeSQL @statement = @createsql
go

Finding common column names in tables for UNION queries

Sometimes if you have tables with a lot of columns which you are trying to union, it can be hard to identify the columns they have in common and you may get this error a lot

Msg 205, Level 16, State 1, Line 1

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target

 One of the nice things you can do using the INFORMATION_SCHEMA views in SQL Server 2008+ is to query the schema to find the columns in common

SELECT  COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS H
WHERE   TABLE_NAME = ''
        AND EXISTS ( SELECT 1
                     FROM   INFORMATION_SCHEMA.COLUMNS W
                     WHERE  TABLE_NAME = ''
                            AND W.COLUMN_NAME = H.COLUMN_NAME )

 This will return the column names that your two tables/views have in common with output like below

You can then use this output to generate a select list for your UNION queries