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