how to extract create table script for a given table dynamically

Question

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

Answer 1

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.