how to extract create table script for a given table dynamically

Nishan Ratna 150 Reputation points
2023-04-06T04:58:03.2666667+00:00

hi all, I want to copy a bunch of tables from one server to another. First I need to create tables. I don't want to do this manually i want to automate the process. so how do I extract create table script for each table of the list dynamically? IF i can get this , i can create the table in destination server using execute sql task in ssis, Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CosmogHong-MSFT 23,641 Reputation points Microsoft Vendor
    2023-04-06T06:21:13.3533333+00:00

    Hi @Nishan Ratna Try this Stored Procedure code:

    CREATE PROC SP_GenerateCreateTable @table_name SYSNAME
    AS
    DECLARE @object_name SYSNAME,@object_id INT
    
    SELECT @object_name = '[' + s.NAME + '].[' + o.NAME + ']',@object_id = o.[object_id]
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE s.NAME + '.' + o.NAME = @table_name
        AND o.[type] = 'U'
        AND o.is_ms_shipped = 0
    
    DECLARE @SQL NVARCHAR(MAX) = ''
    ;WITH index_column
    AS (
        SELECT ic.[object_id]
            ,ic.index_id
            ,ic.is_descending_key
            ,ic.is_included_column
            ,c.NAME
        FROM sys.index_columns ic WITH (NOWAIT)
        JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id]
            AND ic.column_id = c.column_id
        WHERE ic.[object_id] = @object_id
        )
        ,fk_columns
    AS (
        SELECT k.constraint_object_id
            ,cname = c.NAME
            ,rcname = rc.NAME
        FROM sys.foreign_key_columns k WITH (NOWAIT)
        JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id
            AND rc.column_id = k.referenced_column_id
        JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id
            AND c.column_id = k.parent_column_id
        WHERE k.parent_object_id = @object_id
        )
    SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
                SELECT CHAR(9) + ', [' + c.NAME + '] ' + CASE 
                        WHEN c.is_computed = 1
                            THEN 'AS ' + cc.[definition]
                        ELSE UPPER(tp.NAME) + CASE 
                                WHEN tp.NAME IN (
                                        'varchar'
                                        ,'char'
                                        ,'varbinary'
                                        ,'binary'
                                        ,'text'
                                        )
                                    THEN '(' + CASE 
                                            WHEN c.max_length = - 1
                                                THEN 'MAX'
                                            ELSE CAST(c.max_length AS VARCHAR(5))
                                            END + ')'
                                WHEN tp.NAME IN (
                                        'nvarchar'
                                        ,'nchar'
                                        ,'ntext'
                                        )
                                    THEN '(' + CASE 
                                            WHEN c.max_length = - 1
                                                THEN 'MAX'
                                            ELSE CAST(c.max_length / 2 AS VARCHAR(5))
                                            END + ')'
                                WHEN tp.NAME IN (
                                        'datetime2'
                                        ,'time2'
                                        ,'datetimeoffset'
                                        )
                                    THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                                WHEN tp.NAME = 'decimal'
                                    THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                                ELSE ''
                                END + CASE 
                                WHEN c.collation_name IS NOT NULL
                                    THEN ' COLLATE ' + c.collation_name
                                ELSE ''
                                END + CASE 
                                WHEN c.is_nullable = 1
                                    THEN ' NULL'
                                ELSE ' NOT NULL'
                                END + CASE 
                                WHEN dc.[definition] IS NOT NULL
                                    THEN ' DEFAULT' + dc.[definition]
                                ELSE ''
                                END + CASE 
                                WHEN ic.is_identity = 1
                                    THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')'
                                ELSE ''
                                END
                        END + CHAR(13)
                FROM sys.columns c WITH (NOWAIT)
                JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
                LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id]
                    AND c.column_id = cc.column_id
                LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0
                    AND c.[object_id] = dc.parent_object_id
                    AND c.column_id = dc.parent_column_id
                LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1
                    AND c.[object_id] = ic.[object_id]
                    AND c.column_id = ic.column_id
                WHERE c.[object_id] = @object_id
                ORDER BY c.column_id
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ') + ISNULL((
                SELECT CHAR(9) + ', CONSTRAINT [' + k.NAME + '] PRIMARY KEY (' + (
                        SELECT STUFF((
                                    SELECT ', [' + c.NAME + '] ' + CASE 
                                            WHEN ic.is_descending_key = 1
                                                THEN 'DESC'
                                            ELSE 'ASC'
                                            END
                                    FROM sys.index_columns ic WITH (NOWAIT)
                                    JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id]
                                        AND c.column_id = ic.column_id
                                    WHERE ic.is_included_column = 0
                                        AND ic.[object_id] = k.parent_object_id
                                        AND ic.index_id = k.unique_index_id
                                    FOR XML PATH(N'')
                                        ,TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
                        ) + ')' + CHAR(13)
                FROM sys.key_constraints k WITH (NOWAIT)
                WHERE k.parent_object_id = @object_id
                    AND k.[type] = 'PK'
                ), '') + ')' + CHAR(13) + ISNULL((
                SELECT (
                        SELECT CHAR(13) + 'ALTER TABLE ' + @object_name + ' WITH' + CASE 
                                WHEN fk.is_not_trusted = 1
                                    THEN ' NOCHECK'
                                ELSE ' CHECK'
                                END + ' ADD CONSTRAINT [' + fk.NAME + '] FOREIGN KEY(' + STUFF((
                                    SELECT ', [' + k.cname + ']'
                                    FROM fk_columns k
                                    WHERE k.constraint_object_id = fk.[object_id]
                                    FOR XML PATH('')
                                        ,TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.NAME + '] (' + STUFF((
                                    SELECT ', [' + k.rcname + ']'
                                    FROM fk_columns k
                                    WHERE k.constraint_object_id = fk.[object_id]
                                    FOR XML PATH('')
                                        ,TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + CASE 
                                WHEN fk.delete_referential_action = 1
                                    THEN ' ON DELETE CASCADE'
                                WHEN fk.delete_referential_action = 2
                                    THEN ' ON DELETE SET NULL'
                                WHEN fk.delete_referential_action = 3
                                    THEN ' ON DELETE SET DEFAULT'
                                ELSE ''
                                END + CASE 
                                WHEN fk.update_referential_action = 1
                                    THEN ' ON UPDATE CASCADE'
                                WHEN fk.update_referential_action = 2
                                    THEN ' ON UPDATE SET NULL'
                                WHEN fk.update_referential_action = 3
                                    THEN ' ON UPDATE SET DEFAULT'
                                ELSE ''
                                END + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.NAME + ']' + CHAR(13)
                        FROM sys.foreign_keys fk WITH (NOWAIT)
                        JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
                        WHERE fk.parent_object_id = @object_id
                        FOR XML PATH(N'')
                            ,TYPE
                        ).value('.', 'NVARCHAR(MAX)')
                ), '') + ISNULL((
                (
                    SELECT CHAR(13) + 'CREATE' + CASE 
                            WHEN i.is_unique = 1
                                THEN ' UNIQUE'
                            ELSE ''
                            END + ' NONCLUSTERED INDEX [' + i.NAME + '] ON ' + @object_name + ' (' + STUFF((
                                SELECT ', [' + c.NAME + ']' + CASE 
                                        WHEN c.is_descending_key = 1
                                            THEN ' DESC'
                                        ELSE ' ASC'
                                        END
                                FROM index_column c
                                WHERE c.is_included_column = 0
                                    AND c.index_id = i.index_id
                                FOR XML PATH('')
                                    ,TYPE
                                ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + ISNULL(CHAR(13) + 'INCLUDE (' + STUFF((
                                    SELECT ', [' + c.NAME + ']'
                                    FROM index_column c
                                    WHERE c.is_included_column = 1
                                        AND c.index_id = i.index_id
                                    FOR XML PATH('')
                                        ,TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
                    FROM sys.indexes i WITH (NOWAIT)
                    WHERE i.[object_id] = @object_id
                        AND i.is_primary_key = 0
                        AND i.[type] = 2
                    FOR XML PATH('')
                        ,TYPE
                    ).value('.', 'NVARCHAR(MAX)')
                ), '')
    PRINT @SQL
    
    GO
    EXEC SP_GenerateCreateTable 'dbo.TableName' 
    

    Refer to this similar thread: How to get table create script dynamically.