Copy Table DDL Between Servers

This script will allow you to specify a linked server name, a remote database on that linked server, and a local database name in which to copy all the tables from on server onto another.

CREATE PROCEDURE [dba].[pr_refresh_create_tables]
    @linked_server VARCHAR(100),
    @source_db VARCHAR(100),
    @target_db VARCHAR(100),
    @debug_mode INT = 0
AS
-- dba.pr_refresh_create_tables '10.10.60.58', 'yourdbname', 'targetdb', 0
-- SELECT * FROM admindb.dbo.refresh_table_columns
BEGIN

    DECLARE @SQL nvarchar(MAX);
    DECLARE @SQLColumnList nvarchar(MAX);
    DECLARE @SQLSelectColumnList nvarchar(MAX);
    DECLARE @CRLF nvarchar(2);
    DECLARE @Tab nvarchar(1);
    DECLARE @RecordKeyName nvarchar(50);
    DECLARE @RecordKeyDataType nvarchar(50);
    DECLARE @TableID INT;
    DECLARE @TableCount INT;
    DECLARE @SOURCE VARCHAR(MAX) = '[' + @linked_server + '].[' + @source_db + ']'

    DECLARE @TableName VARCHAR(255);


    DECLARE @TableList TABLE (
        TableID INT IDENTITY(1, 1) NOT NULL,
        TableName VARCHAR(255)
        );



    IF OBJECT_ID(N'admindb.dbo.refresh_table_columns_tmp') IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns_tmp
    IF OBJECT_ID(N'admindb.dbo.refresh_table_columns') IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns
    IF OBJECT_ID(N'admindb.tmp.refresh_create_tables') IS NOT NULL DROP TABLE admindb.tmp.refresh_create_tables

    CREATE TABLE admindb.dbo.refresh_table_columns_tmp (
        RowID INT NOT NULL,
        TableName VARCHAR(255),
        ColumnName VARCHAR(255),
        IsIdentity bit,
        DataType VARCHAR(50),
        IsDataTypeCast bit,
        IsNullable bit,
        IsLast bit
        );

    CREATE TABLE admindb.tmp.refresh_create_tables (
        ID INT IDENTITY(1,1),
        TABLE_NAME VARCHAR(255),
        create_table VARCHAR(MAX)
    )


    SET NOCOUNT ON;

    SET @CRLF = CHAR(13) + CHAR(10);
    SET @Tab = CHAR(9);
 

   
SET @SQL =
    'INSERT INTO admindb.dbo.refresh_table_columns_tmp (
        RowID,
        TableName,
        ColumnName,
        IsIdentity,
        DataType,
        IsDataTypeCast,
        IsNullable
        )  
    SELECT
        RowID = ROW_NUMBER() OVER(PARTITION BY t.TableName
            ORDER BY t.IsIdentity DESC, t.ORDINAL_POSITION),
        TableName,
        ColumnName,
        IsIdentity,
        DataType,
        IsDataTypeCast,
        IsNullable
    FROM (
    SELECT DISTINCT
        TableName = c.TABLE_NAME,
        ColumnName = c.COLUMN_NAME,
        IsIdentity = sc.is_identity,
        DataType = '
'''
            + CASE
                WHEN LOWER(c.DATA_TYPE) IN ('
'numeric'', ''decimal'', ''real'')
                THEN LOWER(c.DATA_TYPE) + '
'('' + CAST(c.NUMERIC_PRECISION AS VARCHAR(10)) + '', ''
                    + CAST(c.NUMERIC_SCALE AS VARCHAR(10)) + '
')''
                WHEN LOWER(c.DATA_TYPE) = '
'ntext'' THEN ''nvarchar''
                WHEN LOWER(c.DATA_TYPE) = '
'text'' THEN ''varchar''
                WHEN LOWER(c.DATA_TYPE) = '
'xml'' THEN ''varchar''
                ELSE LOWER(c.DATA_TYPE) END
            + CASE
                WHEN LOWER(c.DATA_TYPE) IN ('
'ntext'', ''text'', ''xml'') THEN ''(MAX)''
                WHEN st.collation_name IS NOT NULL
                    OR LOWER(c.DATA_TYPE) IN ('
'binary'', ''varbinary'') THEN ''(''
                        + REPLACE(CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)), '
'-1'', ''MAX'') + '')''
                ELSE '
''' END,
        IsDataTypeCast = CASE WHEN LOWER(c.DATA_TYPE) IN ('
'ntext'', ''text'', ''xml'') THEN 1 ELSE 0 END,
        IsNullable = CASE c.IS_NULLABLE WHEN '
'NO'' THEN 0 ELSE 1 END,
        ORDINAL_POSITION = c.ORDINAL_POSITION
    FROM admindb.[tmp].[refresh_table_schema_list] t WITH (NOLOCK)
        JOIN '
+ @SOURCE + '.INFORMATION_SCHEMA.COLUMNS c  WITH (NOLOCK)
            ON t.table_name = c.TABLE_NAME
        JOIN  '
+ @SOURCE + '.sys.objects so WITH (NOLOCK)
            ON so.name = t.table_name
        JOIN  '
+ @SOURCE + '.sys.columns sc WITH (NOLOCK)
            ON so.object_id = sc.object_id
            AND c.COLUMN_NAME = sc.[Name]
        JOIN  '
+ @SOURCE + '.sys.types st WITH (NOLOCK)
            ON COALESCE(c.DOMAIN_NAME, c.DATA_TYPE) = st.[Name]
    WHERE LOWER(c.DATA_TYPE) NOT IN ('
'image'') ) t'
   
    EXEC(@SQL)

    SELECT DISTINCT *
    INTO admindb.dbo.refresh_table_columns
    FROM admindb.dbo.refresh_table_columns_tmp

    INSERT INTO @TableList(TableName)
    SELECT DISTINCT TableName
    FROM admindb.dbo.refresh_table_columns

    SET @TableCount = (SELECT COUNT(DISTINCT TableName) FROM admindb.dbo.refresh_table_columns);
    SET @TableID = 1;

    UPDATE rtc
    SET IsLast = 1
    FROM admindb.dbo.refresh_table_columns rtc
    JOIN (
        SELECT TableName, ColumnName, RowID, MaxRow = MAX(RowID) OVER (PARTITION BY TableName)
        FROM admindb.dbo.refresh_table_columns r
    ) r1
    ON rtc.TableName = r1.TableName
    AND rtc.ColumnName = r1.ColumnName
    AND r1.MaxRow = rtc.RowID


    SET @TableID = 1;
    WHILE @TableID <= @TableCount
    BEGIN

        SET @TableName = (SELECT TableName FROM @TableList tl WHERE TableID = @TableID AND EXISTS (SELECT 1 FROM admindb.dbo.refresh_table_columns WHERE TableName = tl.TableName));

        -- Determine Unique Field
        SELECT
            @RecordKeyName = ColumnName,
            @RecordKeyDataType = DataType
        FROM admindb.dbo.refresh_table_columns
        WHERE TableName = @TableName
            AND (IsIdentity = 1 OR ColumnName = @TableName + 'ID' OR ColumnName = @TableName + '_id');
 
        -- Build Column Lists
        SET @SQLColumnList = '';
        SELECT @SQLColumnList = @SQLColumnList
            + '^^^[' + ColumnName + '], ' + @CRLF
        FROM admindb.dbo.refresh_table_columns
        WHERE TableName = @TableName
        ORDER BY RowID;
   
        SET @SQLSelectColumnList = '';
        SELECT @SQLSelectColumnList = @SQLSelectColumnList + CASE IsDataTypeCast
            WHEN 1 THEN '^^^[' + ColumnName + '] = CAST([' + ColumnName + '] AS '+ DataType + '), '
            ELSE '^^^[' + ColumnName + '], '
            END + @CRLF
        FROM admindb.dbo.refresh_table_columns
        WHERE TableName = @TableName
        ORDER BY RowID;

        -- Create Target Table
        SET @SQL = '-- Target Table ' + @CRLF
            + 'IF NOT EXISTS (SELECT * FROM ' + @target_db + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''' + @TableName + ''') ' + @CRLF
            + 'BEGIN ' + @CRLF
            + @Tab + 'CREATE TABLE ' + @target_db + '.[dbo].[' + @TableName + '] ( ' + @CRLF;
        SELECT @SQL = @SQL
            + @Tab + @Tab + '[' + ColumnName + '] ' + DataType + ' '
            + CASE IsIdentity WHEN 1 THEN 'IDENTITY(1, 1) ' ELSE '' END
            + CASE WHEN IsNullable = 0 THEN (CASE WHEN IsLast = 1 THEN 'NOT NULL ' ELSE 'NOT NULL, ' END) ELSE (CASE WHEN IsLast = 1 THEN 'NULL ' ELSE 'NULL, ' END) END + @CRLF

        FROM admindb.dbo.refresh_table_columns
        WHERE TableName = @TableName
        ORDER BY RowID;

        SET @SQL = @SQL + @Tab + @Tab + '); ' + @CRLF + 'END ' + @CRLF


        INSERT INTO admindb.tmp.refresh_create_tables (TABLE_NAME, create_table)
        SELECT @tableName, @SQL

        IF @debug_mode = 0
        BEGIN
            EXEC(@SQL);
        END
        ELSE
        BEGIN
            SELECT @SQL
        END

        SET @TableID = @TableID + 1;
    END


END

Featured Articles

 Site Author

  • Thanks for visiting!