Skripta en temptabell

Innehåll

Att först skapa en tabell innan ett INSERT-skript kan ha dramatiskt mycket bättre prestanda än att skapa tabellen med INSERT-INTO. Men att hålla på att skriva CREATE-skrip med rätt datatyper, nullable osv är för mycket jobb.

Detta skript skapar ett CREATE-skript av en existerande temptabell. Det gör det mycket enkelt att först skapa dina temptabeller med INSERT-INTO, och sedan generera ett CREATE-skript.

Exempel

Resultat av tabell som skapats med
SELECT TOP (0) * INTO #object FROM sys.objects O

CREATE TABLE #object
(
    name                SYSNAME        NOT NULL,
    object_id           INT            NOT NULL,
    principal_id        INT            NULL,
    schema_id           INT            NOT NULL,
    parent_object_id    INT            NOT NULL,
    type                CHAR(2)        NULL,
    type_desc           NVARCHAR(120)  NULL,
    create_date         DATETIME       NOT NULL,
    modify_date         DATETIME       NOT NULL,
    is_ms_shipped       BIT            NOT NULL,
    is_published        BIT            NOT NULL,
    is_schema_published BIT            NOT NULL
);

Skriptet

DECLARE
    @sql NVARCHAR(4000),
    @tmpTableName VARCHAR(50) = '#temptable';

WITH Col AS (
    SELECT
        C.column_id AS [ColId],
        CASE
            WHEN CHARINDEX(' ', C.name) > 0 THEN
                '[' + C.name + ']'
            ELSE
                C.name
        END AS [Colname],
        UPPER(T.name) +
            CASE
                WHEN T.name IN ('varchar', 'nvarchar', 'char', 'nchar', 'binary', 'varbinary') THEN
                    CONCAT('(', C.max_length, ')')
                WHEN T.name IN ('numeric', 'decimal') THEN
                    CONCAT('(', C.precision, ', ', C.scale, ')')
                WHEN T.name IN ('float') THEN
                    CONCAT('(', C.precision, ')')
                WHEN T.name IN ('datetime2') THEN
                    CONCAT('(', C.scale, ')')
                ELSE
                    ''
            END AS [Typedef],
        CASE
            WHEN C.is_nullable = 1 THEN
                ' NULL'
            ELSE
                ' NOT NULL'
        END AS [NUL]
    FROM
        tempdb.sys.columns C
        JOIN sys.types T ON
            T.user_type_id = C.user_type_id
    WHERE
        C.object_id = OBJECT_ID('tempdb..' + @tmpTableName)
),
Colmax AS (
    SELECT
        MAX(LEN(C.Colname)) AS [Colmax],
        MAX(LEN(C.Typedef)) AS [Typemax],
        REPLICATE(' ', 50) AS [Padding]
    FROM
        Col C
)
SELECT
    @sql = 'CREATE TABLE ' + @tmpTableName + CHAR(13) + CHAR(10) + '('+
    STUFF(
        ((
            SELECT
                ',' + CHAR(13) + CHAR(10) + '    ' +
                LEFT(C.Colname + M.padding, M.ColMax + 1) +
                LEFT(C.Typedef + M.padding, M.TypeMax + 1) +
                C.NUL
            FROM
                Col C
                OUTER APPLY Colmax M
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ),
        1,
        1,
        ''
    ) + CHAR(13) + CHAR(10) + ');';
PRINT @sql;