Trying to preserve data types when duplicating a table, dynamically

I'm trying to duplicate a table while preserving the source tables data types, dynamically.

The following query is working, except for the data type of numeric. Numeric data type columns are coming out as nvarchar.

What needs to be changed to preserve the Numeric data types please?

DECLARE @TableName = 'Table'
DECLARE @SchemaName = 'Schema'

DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TablePrefix_' + SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
    [AuditId] INT IDENTITY(1,1) NOT NULL,
    [AuditAction] VARCHAR(50) NOT NULL,
    [AuditDateTime] DATETIME NOT NULL,    
    ' + 
    STUFF((
        SELECT ',' + '[' + c.name + '] ' + 
                CASE 
                    WHEN c.system_type_id IN (167, 175, 231, 239) AND c.max_length = -1 THEN 'VARCHAR(MAX)'
                    WHEN c.system_type_id IN (167, 175, 231, 239) THEN 'VARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (106, 108, 165, 173, 231, 239) AND c.max_length = -1 THEN 'NVARCHAR(MAX)'
                    WHEN c.system_type_id IN (106, 108, 165, 173, 231, 239) THEN 'NVARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length / 2 END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (40) THEN 'CHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (41) THEN 'NCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (48, 52, 56) THEN 'INT'
                    WHEN c.system_type_id IN (127) THEN 'BIGINT'
                    WHEN c.system_type_id IN (59, 60, 62) THEN 'SMALLINT'
                    WHEN c.system_type_id = 104 THEN 'BIT' -- Changed from 'TINYINT' to 'BIT'
                    WHEN c.system_type_id IN (106, 108, 122, 127, 130, 131, 143, 167, 173, 175, 189, 231, 239) THEN TYPE_NAME(c.user_type_id)
                    ELSE TYPE_NAME(c.system_type_id)
                END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
        FROM sys.columns c
        WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
        FOR XML PATH(''), TYPE
    ).value('.[1]','nvarchar(max)'), 1, 1, '')
    + ')'
EXEC(@SQL)

Solution

I rewrote your script a little with corrected data types:

DECLARE @TableName sysname = 't_test'
DECLARE @SchemaName sysname = 'dbo'

CREATE TABLE t_test (i int, n decimal(19,2), z varchar(3), q nvarchar(300), n1 numeric(19,3))

DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TablePrefix_' + SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
    [AuditId] INT IDENTITY(1,1) NOT NULL,
    [AuditAction] VARCHAR(50) NOT NULL,
    [AuditDateTime] DATETIME NOT NULL,    
    ' + 
    STUFF((
        SELECT  ',' + '[' + c.name + '] ' + 
                CASE 
                    WHEN c.system_type_id IN (167, 175, 231, 239) AND c.max_length = -1 THEN 'VARCHAR(MAX)'
                    WHEN c.system_type_id IN (167, 175, 231, 239) THEN 'VARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (165, 173, 231, 239) AND c.max_length = -1 THEN 'NVARCHAR(MAX)'
                    WHEN c.system_type_id IN (165, 173, 231, 239) THEN 'NVARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length / 2 END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (40) THEN 'CHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (41) THEN 'NCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (48, 52, 56) THEN 'INT'
                    WHEN c.system_type_id IN (127) THEN 'BIGINT'
                    WHEN c.system_type_id IN (59, 60, 62) THEN 'SMALLINT'
                    WHEN c.system_type_id IN (106) THEN CONCAT('DECIMAL(', [precision], ',', scale, ')')
                    WHEN c.system_type_id IN (108) THEN CONCAT('NUMERIC(', [precision], ',', scale, ')')
                    WHEN c.system_type_id = 104 THEN 'BIT' -- Changed from 'TINYINT' to 'BIT'
                    ELSE TYPE_NAME(c.system_type_id)
                END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
        FROM    sys.columns c
        WHERE   c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
        ORDER BY c.column_id
        FOR XML PATH(''), TYPE
    ).value('.[1]','nvarchar(max)'), 1, 1, '')
    + ')'

But these kind of scripts usually miss some details and weird datatypes, so lately i've been using dm_exec_describe_first_result_set which handily returns the metadata for you:

SELECT  *
FROM    sys.dm_exec_describe_first_result_set('select * from t_test', NULL, 0)

Another problem with your script is that it doesn't handle user_type_ids, which is prefered way of doing metadatas. For example: sysname is an user defined type for nvarchar, but your script doesn't handle it correctly but dm_exec_describe_first_result_set returns them both.

Also watch out so you include ORDER BY if generating columns, because otherwise you're not guaranteed to get correct sequence of columns