Попытка сохранить типы данных при динамическом дублировании таблицы

Я немного переписал ваш скрипт с исправленными типами данных:

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, '')
    + ')'

Но подобные сценарии обычно упускают некоторые детали и странные типы данных, поэтому в последнее время я использую dm_exec_describe_first_result_set, который легко возвращает вам метаданные:

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

Другая проблема вашего сценария заключается в том, что он не обрабатывает user_type_ids, который является предпочтительным способом создания метаданных. Например: sysname — это определяемый пользователем тип для nvarchar, но ваш скрипт обрабатывает его неправильно, но dm_exec_describe_first_result_set возвращает их оба.

Также следите за тем, чтобы включать ORDER BY при создании столбцов, потому что в противном случае вы не гарантированно получите правильную последовательность столбцов.