如何将表模式和约束复制到不同数据库的表中?[关闭]

当然是两年后,但我想贡献一个解决方案。此过程将表复制到“BackUp_<YYYYMMDD>_<原始表名>及其主键约束中。它用通配符'%'包围表名,以允许复制一组表。我只移动主键,因为不依赖于其他表,因此不太麻烦。此外,没有游标!

ALTER PROCEDURE dbo.Admin_CopyTable
     @TableName NVARCHAR(255)
AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    -- is there any work to do?
    IF NOT (isNull(@TableName,'')='')
    BEGIN
        -- Get list of all tables that match the @TableName wildcard
        DECLARE @tables TABLE (id BIGINT IDENTITY(1,1), [name] NVARCHAR(255)) -- using id as means to avoid cursor
        INSERT INTO @tables SELECT table_name FROM information_schema.tables WHERE table_name LIKE '%' + @TableName + '%' AND table_name NOT LIKE 'BackUp_%' ORDER BY table_name

        -- Go through each table and copy it
        DECLARE @row BIGINT;
        DECLARE @thisTable AS NVARCHAR(255);
        DECLARE @dSQL  NVARCHAR(4000);      -- holds the SQL string to execute and copy table data
        DECLARE @TablePrefix NVARCHAR(255); -- Name that is prepended before the name of the actual table to show it is a backup table

        -- set default prefix by adding todays date to the table name
        SELECT @TablePrefix = 'BackUp_' + CONVERT(NVARCHAR(10), GetDate(), 112) + '_'; -- Date as YYYYMMDD

        -- Get first row for looping through list of tables
        SELECT @row = MIN(id) FROM @tables;

        WHILE (isNull(@row, 0) <> 0)    -- returns null when no more rows, which gets converted to 0 (zero)
        BEGIN
            SELECT @thisTable = [name] FROM @tables where id = @row

            IF NOT EXISTS(SELECT table_name FROM information_schema.tables WHERE table_name like @TablePrefix + @thisTable)
              BEGIN
                SET @dSQL = 'Select * Into ' + @TablePrefix + @thisTable + ' from ' + @thisTable;
                EXEC (@dSQL)
                PRINT @TablePrefix + @thisTable + ': Data Backed up.'

                -- Copy the Primary Key into the Backup table
                BEGIN   
                    -- Get list of all the columns that make up the Primary Key
                    -- for composite PK's = one row per pk column
                    DECLARE @PKColsTbl table ([name] NVARCHAR(255), col NVARCHAR(255))

                    INSERT INTO @PKColsTbl
                    SELECT  c.Constraint_Name, c.COLUMN_NAME  
                    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
                           ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
                    WHERE   pk.TABLE_NAME = @thisTable 
                        and CONSTRAINT_TYPE = 'PRIMARY KEY' 
                        and c.TABLE_NAME = pk.TABLE_NAME 
                        and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 

                    -- Transpose rows into one column as a comma delimeted string
                    DECLARE @pkCol NVARCHAR(255);
                    SELECT @pkCol = stuff((select ',' + col from @PKColsTbl for XML PATH('')), 1,1,'')

                    -- Build the dynamic SQL statement and execute it
                    SET @dSQL = 'ALTER TABLE ' + @TablePrefix + @thisTable + ' ADD CONSTRAINT ' + 'PK_' + @TablePrefix + @thisTable + ' PRIMARY KEY (' + @pkCol + ')'
                    EXEC (@dSQL)
                    PRINT @TablePrefix + @thisTable + ': PK Created.'

                    -- Since its a loop, clear out the table
                    DELETE FROM @PKColsTbl
                END
                -- END of Copying the Primary Key

              END
            ELSE 
              BEGIN
                PRINT @TablePrefix + @thisTable + ': Exists!'
              END

            -- Get next row
            SELECT @row = min(id) FROM @tables WHERE id > @row
        END
    END
END

SELECT INTO 将创建一个具有相同架构的新表。所以你可以:

 SELECT *
 INTO newdb.dbo.newtable
 FROM olddb.dbo.oldtable

只复制模式而不复制数据:

 SELECT TOP 0 *
 INTO newdb.dbo.newtable
 FROM olddb.dbo.oldtable

这不会复制索引或键。要复制这些,请在SQL Server Management Studio中右键单击该表,然后选择“将表脚本化为”。这将为您提供一个可以在新数据库上运行的脚本。

尝试使用SQL SERVER脚本WIZARD

1) 选择数据库

2) 单击鼠标右键->任务->生成脚本

3) 单击下一步->在选择数据库屏幕中,选择表(默认情况下将被选中)。选中“为选定数据库中的所有对象编写脚本”。

4) 单击“完成”。

希望这有帮助

我编写这个sp是为了自动创建包含所有内容、pk、fk、分区和约束的模式。..

重要!! 在执行之前

    create type TestTableType as table (ObjectID int)

这里是SP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--*************************************************************************************************'
--    La procedura crea lo script di una tabella

--*************************************************************************************************'



alter PROCEDURE [dbo].[util_ScriptTable] 
     @DBName SYSNAME
    ,@schema sysname
    ,@TableName SYSNAME
    ,@IncludeConstraints BIT = 1
    ,@IncludeIndexes BIT = 1
    ,@NewTableSchema sysname
    ,@NewTableName SYSNAME = NULL
    ,@UseSystemDataTypes BIT = 0
    ,@script varchar(max) output
AS 
BEGIN try
    if not exists (select * from sys.types where name = 'TestTableType')
        create type TestTableType as table (ObjectID int)--drop type TestTableType

    declare @sql nvarchar(max)

    DECLARE @MainDefinition TABLE (FieldValue VARCHAR(200))
    --DECLARE @DBName SYSNAME
    DECLARE @ClusteredPK BIT
    DECLARE @TableSchema NVARCHAR(255)

    --SET @DBName = DB_NAME(DB_ID())
    SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

    DECLARE @ShowFields TABLE (FieldID INT IDENTITY(1,1)
                                        ,DatabaseName VARCHAR(100)
                                        ,TableOwner VARCHAR(100)
                                        ,TableName VARCHAR(100)
                                        ,FieldName VARCHAR(100)
                                        ,ColumnPosition INT
                                        ,ColumnDefaultValue VARCHAR(100)
                                        ,ColumnDefaultName VARCHAR(100)
                                        ,IsNullable BIT
                                        ,DataType VARCHAR(100)
                                        ,MaxLength varchar(10)
                                        ,NumericPrecision INT
                                        ,NumericScale INT
                                        ,DomainName VARCHAR(100)
                                        ,FieldListingName VARCHAR(110)
                                        ,FieldDefinition CHAR(1)
                                        ,IdentityColumn BIT
                                        ,IdentitySeed INT
                                        ,IdentityIncrement INT
                                        ,IsCharColumn BIT 
                                        ,IsComputed varchar(255))

    DECLARE @HoldingArea TABLE(FldID SMALLINT IDENTITY(1,1)
                                        ,Flds VARCHAR(4000)
                                        ,FldValue CHAR(1) DEFAULT(0))

    DECLARE @PKObjectID TABLE(ObjectID INT)

    DECLARE @Uniques TABLE(ObjectID INT)

    DECLARE @HoldingAreaValues TABLE(FldID SMALLINT IDENTITY(1,1)
                                                ,Flds VARCHAR(4000)
                                                ,FldValue CHAR(1) DEFAULT(0))

    DECLARE @Definition TABLE(DefinitionID SMALLINT IDENTITY(1,1)
                                        ,FieldValue VARCHAR(200))


  set @sql=
  '
  use '+@DBName+'
  SELECT distinct DB_NAME()
            ,TABLE_SCHEMA
            ,TABLE_NAME
            ,''[''+COLUMN_NAME+'']'' as COLUMN_NAME
            ,CAST(ORDINAL_POSITION AS INT)
            ,COLUMN_DEFAULT
            ,dobj.name AS ColumnDefaultName
            ,CASE WHEN c.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END
            ,DATA_TYPE
            ,case CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) end--CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
            ,CAST(NUMERIC_PRECISION AS INT)
            ,CAST(NUMERIC_SCALE AS INT)
            ,DOMAIN_NAME
            ,COLUMN_NAME + '',''
            ,'''' AS FieldDefinition
            ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
            ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
            ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
            ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
            ,cc.definition 
            FROM INFORMATION_SCHEMA.COLUMNS c
            JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
            LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
            JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
            LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = ''D''
            left join sys.computed_columns cc on c.TABLE_NAME=OBJECT_NAME(cc.object_id) and sc.column_id=cc.column_id
    WHERE c.TABLE_NAME = @TableName and c.TABLE_SCHEMA=@schema 
    ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
    '

  print @sql
  INSERT INTO @ShowFields( DatabaseName
                                    ,TableOwner
                                    ,TableName
                                    ,FieldName
                                    ,ColumnPosition
                                    ,ColumnDefaultValue
                                    ,ColumnDefaultName
                                    ,IsNullable
                                    ,DataType
                                    ,MaxLength
                                    ,NumericPrecision
                                    ,NumericScale
                                    ,DomainName
                                    ,FieldListingName
                                    ,FieldDefinition
                                    ,IdentityColumn
                                    ,IdentitySeed
                                    ,IdentityIncrement
                                    ,IsCharColumn
                                    ,IsComputed)

    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName,@schema=@schema            
    /*
    SELECT @DBName--DB_NAME()
            ,TABLE_SCHEMA
            ,TABLE_NAME
            ,COLUMN_NAME
            ,CAST(ORDINAL_POSITION AS INT)
            ,COLUMN_DEFAULT
            ,dobj.name AS ColumnDefaultName
            ,CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END
            ,DATA_TYPE
            ,CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
            ,CAST(NUMERIC_PRECISION AS INT)
            ,CAST(NUMERIC_SCALE AS INT)
            ,DOMAIN_NAME
            ,COLUMN_NAME + ','
            ,'' AS FieldDefinition
            ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
            ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
            ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
            ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
            FROM INFORMATION_SCHEMA.COLUMNS c
            JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
            LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
            JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
            LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'

    WHERE c.TABLE_NAME = @TableName
    ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
    */
    SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields

    INSERT INTO @HoldingArea (Flds) VALUES('(')

    INSERT INTO @Definition(FieldValue)VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @DBName + '.' + @NewTableSchema + '.' + @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)
    INSERT INTO @Definition(FieldValue)VALUES('(')
    INSERT INTO @Definition(FieldValue)
    SELECT   CHAR(10) + FieldName + ' ' + 
        --CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) +CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END 

        CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + 
            CASe WHEN IsNullable = 1 THEN ' NULL ' 
            ELSE ' NOT NULL ' 
            END 
        ELSE 
            case when IsComputed is null then
                UPPER(DataType) +
                CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' 
                ELSE 
                    CASE WHEN DataType = 'numeric' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                    ELSE
                        CASE WHEN DataType = 'decimal' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                        ELSE '' 
                        end  
                    end 
                END +
                CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' 
                ELSE '' 
                END +
                CASE WHEN IsNullable = 1 THEN ' NULL ' 
                ELSE ' NOT NULL ' 
                END +
                CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + replace(ColumnDefaultName,@TableName,@NewTableName) + '] DEFAULT' + UPPER(ColumnDefaultValue) 
                ELSE '' 
                END 
            else
                ' as '+IsComputed+' '
            end
        END + 
        CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' 
        ELSE ',' 
        END 

    FROM    @ShowFields

    IF @IncludeConstraints = 1
        BEGIN    

        set @sql=
        '
        use '+@DBName+'
        SELECT  distinct  '',CONSTRAINT ['' + replace(name,@TableName,@NewTableName) + ''] FOREIGN KEY ('' + ParentColumns + '') REFERENCES ['' + ReferencedObject + '']('' + ReferencedColumns + '')'' 
           FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + '',''   
                FROM   sys.foreign_key_columns fkc   
                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ParentColumns,   
                REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + '',''   
                FROM   sys.foreign_key_columns fkc  
                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ReferencedColumns   
                FROM sys.foreign_keys fk    
                    inner join sys.schemas s on fk.schema_id=s.schema_id and s.name=@schema) a    
            WHERE ParentObject = @TableName    
        '

        print @sql

        INSERT INTO @Definition(FieldValue)
        exec sp_executesql @sql,
                   N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema
            /*
           SELECT    ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'  
           FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + ','   
                FROM   sys.foreign_key_columns fkc   
                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ParentColumns,   
                REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + ','   
                FROM   sys.foreign_key_columns fkc  
                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ReferencedColumns   
                FROM sys.foreign_keys fk    ) a    
            WHERE ParentObject = @TableName    
            */

            set @sql=
            '
            use '+@DBName+'
            SELECT distinct '',CONSTRAINT ['' + replace(c.name,@TableName,@NewTableName) + ''] CHECK '' + definition 
            FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and s.name=@schema    
            WHERE OBJECT_NAME(parent_object_id) = @TableName
            '

            print @sql
            INSERT INTO @Definition(FieldValue) 
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema
            /*
            SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints    
            WHERE OBJECT_NAME(parent_object_id) = @TableName
            */

            set @sql=
            '
            use '+@DBName+'
            SELECT DISTINCT  PKObject = cco.object_id 
            FROM    sys.key_constraints cco    
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
            WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
            '
            print @sql
            INSERT INTO @PKObjectID(ObjectID) 
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@schema varchar(50)',
                               @TableName=@TableName,@schema=@schema
            /*
            SELECT DISTINCT  PKObject = cco.object_id 
            FROM    sys.key_constraints cco    
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
            */

            set @sql=
            '
            use '+@DBName+'
            SELECT DISTINCT    PKObject = cco.object_id
            FROM    sys.key_constraints cco   
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
            WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
            '
            print @sql
            INSERT INTO @Uniques(ObjectID)
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@schema varchar(50)',
                               @TableName=@TableName,@schema=@schema
            /*
            SELECT DISTINCT    PKObject = cco.object_id
            FROM    sys.key_constraints cco   
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
            */

            SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

            declare @t TestTableType
            insert @t select * from @PKObjectID
            declare @u TestTableType
            insert @u select * from @Uniques

            set @sql=
            '
            use '+@DBName+'
            SELECT distinct '',CONSTRAINT '' + replace(cco.name,@TableName,@NewTableName) + CASE type WHEN ''PK'' THEN '' PRIMARY KEY '' + CASE WHEN pk.ObjectID IS NULL THEN '' NONCLUSTERED '' ELSE '' CLUSTERED '' END  WHEN ''UQ'' THEN '' UNIQUE '' END + CASE WHEN u.ObjectID IS NOT NULL THEN '' NONCLUSTERED '' ELSE '''' END 
            + ''(''+REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''    
            FROM   sys.key_constraints ccok   
            LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
            LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
            LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
            WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    
            order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')''
            FROM sys.key_constraints cco 
            inner join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
            LEFT JOIN @U u ON cco.object_id = u.objectID
            LEFT JOIN @t pk ON cco.object_id = pk.ObjectID    
            WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 

            '

            print @sql
            INSERT INTO @Definition(FieldValue)
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly',
                               @TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema,@t=@t,@u=@u

            /*
            SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END  WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END 
            + '(' +REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','    
            FROM   sys.key_constraints ccok   
            LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
           LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
           LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
           WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    FOR XML PATH(''))), 2, 8000)) + ')'
           FROM sys.key_constraints cco 
           LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID    
           LEFT JOIN @Uniques u ON cco.object_id = u.objectID
           WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 
           */
        END

        INSERT INTO @Definition(FieldValue) VALUES(')')

        set @sql=
        '
        use '+@DBName+'
        select '' on '' + d.name + ''([''+c.name+''])''
        from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2)
                          join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id)
                          join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id)
                          join sys.schemas s on t.schema_id=s.schema_id
                          join sys.data_spaces d on i.data_space_id=d.data_space_id
        where t.name=@TableName and s.name=@schema
        order by key_ordinal
        '

        print 'x'
        print @sql
        INSERT INTO @Definition(FieldValue) 
        exec sp_executesql @sql,
                           N'@TableName varchar(50),@schema varchar(50)',
                           @TableName=@TableName,@schema=@schema

        IF @IncludeIndexes = 1
        BEGIN
            set @sql=
            '
            use '+@DBName+'
            SELECT distinct '' CREATE '' + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '+@DBName+'.'+@NewTableSchema+'.'+@NewTableName+' ('' 
            +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=0
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
            ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=1
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
            FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                               join sys.schemas s on t.schema_id=s.schema_id   
            AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
                where t.name=@TableName and s.name=@schema
            '

            print @sql
            INSERT INTO @Definition(FieldValue)    
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit',
                               @TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema,@ClusteredPK=@ClusteredPK

        END 

            /*

                SELECT   'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  OBJECT_NAME(sc.object_id) = @TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                ORDER BY index_column_id ASC   FOR XML PATH('')    )), 2, 8000)) + ')'    
                FROM sys.indexes i    
                WHERE   OBJECT_NAME(object_id) = @TableName
                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 

            */

            INSERT INTO @MainDefinition(FieldValue)   
            SELECT FieldValue FROM @Definition    
            ORDER BY DefinitionID ASC 

            ----------------------------------

            declare @q  varchar(max)

            set @q=(select replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')),'</FieldValue>',''))

            set @script=(select REPLACE(@q,'<FieldValue>',''))
            --drop type TestTableType
END try
-- ##############################################################################################################################################################################
BEGIN CATCH        
    BEGIN
        -- INIZIO  Procedura in errore =========================================================================================================================================================
            PRINT '***********************************************************************************************************************************************************' 
            PRINT 'ErrorNumber               : ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX))
            PRINT 'ErrorSeverity             : ' + CAST(ERROR_SEVERITY() AS NVARCHAR(MAX)) 
            PRINT 'ErrorState                : ' + CAST(ERROR_STATE() AS NVARCHAR(MAX)) 
            PRINT 'ErrorLine                 : ' + CAST(ERROR_LINE() AS NVARCHAR(MAX)) 
            PRINT 'ErrorMessage              : ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX))
            PRINT '***********************************************************************************************************************************************************' 
        -- FINE  Procedura in errore =========================================================================================================================================================
    END 
        set @script=''
    return -1
END CATCH   
-- ##############################################################################################################################################################################   

执行它:

declare @s varchar(max)
exec [util_ScriptTable]   'db','schema_source','table_source',1,1,'schema_dest','tab_dest',0,@s output
select @s

在这里sp找到不同于源做目标:

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--*************************************************************************************************'
--    confronta 2 tabelle e fornisce lo scrit di aggiornamento
--    Tabella   : xxxxx
--    Creata da : E.Mantovanelli
--    Data creazione : 26-11-2013
--    Data modifica: 26-11-2013
--*************************************************************************************************'

/*
    --      nn supporta il caso inm cui si rinomini una colonna(colonne nn coincidenti, esegue drop+add)

    --      ID----|-----Data-----|--    User        ---------   | ----  Note

*/

ALTER PROCEDURE [dbo].[util_ScriptTable_Update] 
     @DBName SYSNAME
    ,@schema_1 sysname
    ,@TableName_1 SYSNAME
    ,@IncludeConstraints BIT = 1
    ,@IncludeIndexes BIT = 1
    ,@Schema_2 sysname
    ,@TableName_2 SYSNAME = NULL
    ,@UseSystemDataTypes BIT = 0
    ,@script varchar(max) output
AS 
BEGIN try
    if not exists (select * from sys.types where name = 'TestTableType')
        create type TestTableType as table (ObjectID int)--drop type TestTableType

    declare @sql nvarchar(max),
            @t0 varchar(max),
            @t1 varchar(max),
            @t2 varchar(max),
            @script_add varchar(max),
            @script_drop varchar(max)

    DECLARE @MainDefinition TABLE (FieldValue VARCHAR(max))
    DECLARE @ClusteredPK_1 BIT,
            @ClusteredPK_2 BIT

    DECLARE @TableSchema NVARCHAR(255)
    declare @partition varchar(max)

    --SET @DBName = DB_NAME(DB_ID())
    SELECT @TableName_1 = name FROM sysobjects WHERE id = OBJECT_ID(@TableName_1)

    DECLARE @ShowFields_1 TABLE (FieldID INT IDENTITY(1,1)
                                        ,DatabaseName VARCHAR(100)
                                        ,TableOwner VARCHAR(100)
                                        ,TableName VARCHAR(100)
                                        ,FieldName VARCHAR(max)
                                        ,ColumnPosition INT
                                        ,ColumnDefaultValue VARCHAR(100)
                                        ,ColumnDefaultName VARCHAR(100)
                                        ,IsNullable BIT
                                        ,DataType VARCHAR(100)
                                        ,MaxLength varchar(10)
                                        ,NumericPrecision INT
                                        ,NumericScale INT
                                        ,DomainName VARCHAR(100)
                                        ,FieldListingName VARCHAR(110)
                                        ,FieldDefinition CHAR(1)
                                        ,IdentityColumn BIT
                                        ,IdentitySeed INT
                                        ,IdentityIncrement INT
                                        ,IsCharColumn BIT 
                                        ,IsComputed varchar(255))

    DECLARE @ShowFields_2 TABLE (FieldID INT IDENTITY(1,1)
                                        ,DatabaseName VARCHAR(100)
                                        ,TableOwner VARCHAR(100)
                                        ,TableName VARCHAR(100)
                                        ,FieldName VARCHAR(max)
                                        ,ColumnPosition INT
                                        ,ColumnDefaultValue VARCHAR(100)
                                        ,ColumnDefaultName VARCHAR(100)
                                        ,IsNullable BIT
                                        ,DataType VARCHAR(100)
                                        ,MaxLength varchar(10)
                                        ,NumericPrecision INT
                                        ,NumericScale INT
                                        ,DomainName VARCHAR(100)
                                        ,FieldListingName VARCHAR(110)
                                        ,FieldDefinition CHAR(1)
                                        ,IdentityColumn BIT
                                        ,IdentitySeed INT
                                        ,IdentityIncrement INT
                                        ,IsCharColumn BIT 
                                        ,IsComputed varchar(255))

    DECLARE @HoldingArea TABLE(FldID SMALLINT IDENTITY(1,1)
                                        ,Flds VARCHAR(4000)
                                        ,FldValue CHAR(1) DEFAULT(0))

    DECLARE @PKObjectID TABLE(ObjectID INT)

    DECLARE @Uniques TABLE(ObjectID INT)

    DECLARE @HoldingAreaValues TABLE(FldID SMALLINT IDENTITY(1,1)
                                                ,Flds VARCHAR(4000)
                                                ,FldValue CHAR(1) DEFAULT(0))

    DECLARE @Definition TABLE(--DefinitionID SMALLINT IDENTITY(1,1)
                                         state int
                                        ,FieldValue_1 VARCHAR(max),FieldValue_2 VARCHAR(max))

    ------------------------------
    --FK
    ------------------------------

    delete @Definition
    delete @ShowFields_1
    delete @ShowFields_2

    set @sql=
    '
    use '+@DBName+'
    SELECT distinct db_name(),@schema,ParentObject,name, ''('' + ParentColumns + '') REFERENCES ['' + ReferencedObject + '']('' + ReferencedColumns + '')'' 
       FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
            ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + '',''   
            FROM   sys.foreign_key_columns fkc   
            JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
            WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ParentColumns,   
            REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + '',''   
            FROM   sys.foreign_key_columns fkc  
            JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
            WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ReferencedColumns   
            FROM sys.foreign_keys fk    
                inner join sys.schemas s on fk.schema_id=s.schema_id and s.name=@schema) a    
        WHERE ParentObject = @TableName    
    '

    print @sql

    INSERT INTO @ShowFields_1( DatabaseName
                              ,TableOwner
                              ,TableName
                              ,ColumnDefaultName
                              ,FieldName
                              )
    exec sp_executesql @sql,
               N'@TableName varchar(50),@schema varchar(50)',
                 @TableName=@tablename_1,@schema=@schema_1

    INSERT INTO @ShowFields_2( DatabaseName
                              ,TableOwner
                              ,TableName
                              ,ColumnDefaultName
                              ,FieldName
                              )
    exec sp_executesql @sql,
               N'@TableName varchar(50),@schema varchar(50)',
                 @TableName=@tablename_2,@schema=@schema_2  

    INSERT INTO @Definition(FieldValue_1)
    select '['+@TableName_2+'_'+RIGHT(substring(convert(varchar(10),RAND()),3,10),10)+'] FOREIGN KEY '+FieldName+','
    from
    (
        select COUNT(*)c,MAX(TableOwner)s,MAX(TableName)t,FieldName from
        ( 
            select * from @ShowFields_1 union all
            select * from @ShowFields_2 
        )t 
        group by FieldName          
    )t
    where c=1 and s=@schema_1 and t=@TableName_1

    INSERT INTO @Definition(FieldValue_2)
    select '['+k+'],'
    from
    (
        select COUNT(*)c,MAX(TableOwner)s,MAX(TableName)t,MAX(ColumnDefaultName)k,FieldName from
        ( 
            select * from @ShowFields_1 union all
            select * from @ShowFields_2 
        )t 
        group by FieldName          
    )t
    where c=1 and s=@schema_2 and t=@TableName_2

    set @t1=case when (select COUNT(FieldValue_1) from @Definition)>0 then (select ' alter table '+@Schema_2+'.'+@TableName_2+' add CONSTRAINT'+(replace((select replace((SELECT SUBSTRING(FieldValue_1,0,LEN(FieldValue_1)+1) FROM @Definition FOR XML PATH('')),'</FieldValue_1>','')),'<FieldValue_1>',''))) else '' end
    set @t2=case when (select COUNT(FieldValue_2) from @Definition)>0 then (select ' alter table '+@Schema_2+'.'+@TableName_2+' drop CONSTRAINT '+(replace((select replace((SELECT SUBSTRING(FieldValue_2,0,LEN(FieldValue_2)+1) FROM @Definition FOR XML PATH('')),'</FieldValue_2>','')),'<FieldValue_2>',''))) else '' end

    --prima la drop, poi la add
    set @script_drop =' '+ substring(@t2,0,len(@t2))
    set @script_add  =' '+ substring(@t1,0,len(@t1))

    -----------------------------
    --Keys
    -----------------------------

    delete @Definition
    delete @ShowFields_1
    delete @ShowFields_2

    set @sql=
    '
    use '+@DBName+'
    SELECT DISTINCT  PKObject = cco.object_id 
    FROM    sys.key_constraints cco    
    JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
    JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
    join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
    WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
    '
    print @sql
    INSERT INTO @PKObjectID(ObjectID) 
    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName_1,@schema=@schema_1

    print @sql
    INSERT INTO @PKObjectID(ObjectID) 
    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName_2,@schema=@schema_2
    /*
    SELECT DISTINCT  PKObject = cco.object_id 
    FROM    sys.key_constraints cco    
    JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
    JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
    WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
    */

    set @sql=
    '
    use '+@DBName+'
    SELECT DISTINCT    PKObject = cco.object_id
    FROM    sys.key_constraints cco   
    JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
    JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
    join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
    WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
    '
    print @sql
    INSERT INTO @Uniques(ObjectID)
    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName_1,@schema=@schema_1

    SET @ClusteredPK_1 = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

    print @sql
    INSERT INTO @Uniques(ObjectID)
    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName_2,@schema=@schema_2

    SET @ClusteredPK_2 = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

    --------------------------------------
    --PK
    --------------------------------------

    delete @Definition
    delete @ShowFields_1
    delete @ShowFields_2

    set @sql=
    '
    use '+@DBName+'
    SELECT distinct db_name(),@schema,@TableName,cco.name,CASE type WHEN ''PK'' THEN '' PRIMARY KEY '' + CASE WHEN pk.ObjectID IS NULL THEN '' NONCLUSTERED '' ELSE '' CLUSTERED '' END  WHEN ''UQ'' THEN '' UNIQUE '' END + CASE WHEN u.ObjectID IS NOT NULL THEN '' NONCLUSTERED '' ELSE '''' END 
            + ''(''+REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''    
            FROM   sys.key_constraints ccok   
            LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
            LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
            LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
            WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    
            order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')''
            FROM sys.key_constraints cco 
            inner join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
            LEFT JOIN @U u ON cco.object_id = u.objectID
            LEFT JOIN @t pk ON cco.object_id = pk.ObjectID    
            WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 
    '

    declare @t TestTableType
    insert @t select * from @PKObjectID
    declare @u TestTableType
    insert @u select * from @Uniques

    print @sql

    INSERT INTO @ShowFields_1( DatabaseName
                              ,TableOwner
                              ,TableName
                              ,ColumnDefaultName
                              ,FieldName
                              )
    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly',
                       @TableName=@TableName_1,@schema=@schema_1,@t=@t,@u=@u

    INSERT INTO @ShowFields_2( DatabaseName
                              ,TableOwner
                              ,TableName
                              ,ColumnDefaultName
                              ,FieldName
                              )
    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly',
                       @TableName=@TableName_2,@schema=@schema_2,@t=@t,@u=@u

    INSERT INTO @Definition(FieldValue_1)
    select ' alter table '+@Schema_2+'.'+@TableName_2+' add constraint ['+k+'] '+FieldName+';'
    from
    (
        select COUNT(*)c,MAX(TableOwner)s,MAX(TableName)t,MAX(ColumnDefaultName)k,FieldName from
        ( 
            select * from @ShowFields_1 union all
            select * from @ShowFields_2 
        )t 
        group by FieldName          
    )t
    where c=1 and s=@schema_1 and t=@TableName_1

    INSERT INTO @Definition(FieldValue_2)
    select ' alter table '+@Schema_2+'.'+@TableName_2+' drop constraint ['+k+'] ;'
    from
    (
        select COUNT(*)c,MAX(TableOwner)s,MAX(TableName)t,MAX(ColumnDefaultName)k,FieldName from
        ( 
            select * from @ShowFields_1 union all
            select * from @ShowFields_2 
        )t 
        group by FieldName          
    )t
    where c=1 and s=@schema_2 and t=@TableName_2

    set @t1=case when (select COUNT(FieldValue_1) from @Definition)>0 then ((replace((select replace((SELECT SUBSTRING(FieldValue_1,0,LEN(FieldValue_1)+1) FROM @Definition FOR XML PATH('')),'</FieldValue_1>','')),'<FieldValue_1>',''))) else '' end
    set @t2=case when (select COUNT(FieldValue_2) from @Definition)>0 then ((replace((select replace((SELECT SUBSTRING(FieldValue_2,0,LEN(FieldValue_2)+1) FROM @Definition FOR XML PATH('')),'</FieldValue_2>','')),'<FieldValue_2>',''))) else '' end

    --prima la drop, poi la add
    set @script_drop +=' '+ substring(@t2,0,len(@t2))
    set @script_add  =substring(@t1,0,len(@t1))+ ' ' +@script_add


    --------------------------------------
    --Partition
    --------------------------------------

    delete @Definition
    delete @ShowFields_1
    delete @ShowFields_2

    set @sql=
    '
    use '+@DBName+'
    select @partition='' on '' + d.name + ''([''+c.name+''])''
    from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2)
                      join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id)
                      join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id)
                      join sys.schemas s on t.schema_id=s.schema_id
                      join sys.data_spaces d on i.data_space_id=d.data_space_id
    where t.name=@TableName and s.name=@schema
    order by key_ordinal
    '

    print 'x'
    print @sql
    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50),@partition varchar(max) output',
                       @TableName=@TableName_1,@schema=@schema_1,@partition=@partition output

    if(@partition is null)
        set @partition=''

    --select @partition     

    --------------------------------------
    --Indexes
    --------------------------------------

    delete @Definition
    delete @ShowFields_1
    delete @ShowFields_2

    IF @IncludeIndexes = 1
    BEGIN
        set @sql=
        '
        use '+@DBName+'
        SELECT DB_NAME(),s.name,t.name,i.name COLLATE SQL_Latin1_General_CP1_CI_AS, i.type_desc ,''(''+REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
        FROM  sys.index_columns sc  
        JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
        WHERE  t.name=@TableName AND sc.object_id = i.object_id 
                                 AND  sc.index_id = i.index_id   
                                 and  t.schema_id=s.schema_id
                                 and is_included_column=0
                                 --caso di tabelle partizionate
                                 --inserisce in automatico la pk
                                 and (key_ordinal!=0 or partition_ordinal!=1)
        ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
        ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
        FROM  sys.index_columns sc  
        JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
        WHERE  t.name=@TableName AND  sc.object_id = i.object_id 
                                 AND  sc.index_id = i.index_id 
                                 and  t.schema_id=s.schema_id  
                                 and is_included_column=1
        ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
        FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                           join sys.schemas s on t.schema_id=s.schema_id   
        AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
            where t.name=@TableName and s.name=@schema
        '

        print @ClusteredPK_1
        print @sql
        INSERT INTO @ShowFields_1( DatabaseName
                                  ,TableOwner
                                  ,TableName
                                  ,ColumnDefaultName
                                  ,ColumnDefaultValue
                                  ,FieldName
                                  )
        exec sp_executesql @sql,
                           N'@TableName varchar(50),@schema varchar(50), @ClusteredPK bit',
                           @TableName=@TableName_1,@schema=@schema_1,@ClusteredPK=@ClusteredPK_1


        INSERT INTO @ShowFields_2( DatabaseName
                                  ,TableOwner
                                  ,TableName
                                  ,ColumnDefaultName
                                  ,ColumnDefaultValue
                                  ,FieldName
                                  )
        exec sp_executesql @sql,
                           N'@TableName varchar(50),@schema varchar(50), @ClusteredPK bit',
                           @TableName=@TableName_2,@schema=@schema_2,@ClusteredPK=@ClusteredPK_2


        INSERT INTO @Definition(FieldValue_1)
        select 'create '+ColumnDefaultValue+' index ['+n+'] on '+@schema_2+'.'+@TableName_2+FieldName+@partition+';'
        from
        (
            select COUNT(*)c,MAX(TableOwner)s,MAX(TableName)t,MAX(ColumnDefaultName)n,ColumnDefaultValue,FieldName from
            ( 
                select * from @ShowFields_1 union all
                select * from @ShowFields_2 
            )t 
            group by ColumnDefaultValue,FieldName           
        )t
        where c=1 and s=@schema_1 and t=@TableName_1

        INSERT INTO @Definition(FieldValue_2)
        select 'drop index ['+n+'] on '+@schema_2+'.'+@TableName_2+' ;'
        from
        (
            select COUNT(*)c,MAX(TableOwner)s,MAX(TableName)t,MAX(ColumnDefaultName)n,ColumnDefaultValue,FieldName from
            ( 
                select * from @ShowFields_1 union all
                select * from @ShowFields_2 
            )t 
            group by ColumnDefaultValue,FieldName           
        )t
        where c=1 and s=@schema_2 and t=@TableName_2

        set @t1=case when (select COUNT(FieldValue_1) from @Definition)>0 then ((replace((select replace((SELECT SUBSTRING(FieldValue_1,0,LEN(FieldValue_1)+1) FROM @Definition FOR XML PATH('')),'</FieldValue_1>','')),'<FieldValue_1>',''))) else '' end
        set @t2=case when (select COUNT(FieldValue_2) from @Definition)>0 then ((replace((select replace((SELECT SUBSTRING(FieldValue_2,0,LEN(FieldValue_2)+1) FROM @Definition FOR XML PATH('')),'</FieldValue_2>','')),'<FieldValue_2>',''))) else '' end

        --prima la drop, poi la add
        set @script_drop +=' '+ substring(@t2,0,len(@t2))
        set @script_add  =substring(@t1,0,len(@t1))+ ' ' +@script_add

    END     
    -----------------------------
    --Columns
    -----------------------------

    delete @Definition
    delete @ShowFields_1
    delete @ShowFields_2

  set @sql=
  '
  use '+@DBName+'
  SELECT distinct DB_NAME()
            ,TABLE_SCHEMA
            ,TABLE_NAME
            ,''[''+COLUMN_NAME+'']'' as COLUMN_NAME
            ,CAST(ORDINAL_POSITION AS INT)
            ,COLUMN_DEFAULT
            ,dobj.name AS ColumnDefaultName
            ,CASE WHEN c.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END
            ,DATA_TYPE
            ,case CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) end--CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
            ,CAST(NUMERIC_PRECISION AS INT)
            ,CAST(NUMERIC_SCALE AS INT)
            ,DOMAIN_NAME
            ,COLUMN_NAME + '',''
            ,'''' AS FieldDefinition
            ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
            ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
            ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
            ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
            ,cc.definition 
            FROM INFORMATION_SCHEMA.COLUMNS c
            JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
            LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
            JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
            LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = ''D''
            left join sys.computed_columns cc on c.TABLE_NAME=OBJECT_NAME(cc.object_id) and sc.column_id=cc.column_id
    WHERE c.TABLE_NAME = @TableName and c.TABLE_SCHEMA=@schema 
    ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
    '

  print @sql
  INSERT INTO @ShowFields_1( DatabaseName
                                    ,TableOwner
                                    ,TableName
                                    ,FieldName
                                    ,ColumnPosition
                                    ,ColumnDefaultValue
                                    ,ColumnDefaultName
                                    ,IsNullable
                                    ,DataType
                                    ,MaxLength
                                    ,NumericPrecision
                                    ,NumericScale
                                    ,DomainName
                                    ,FieldListingName
                                    ,FieldDefinition
                                    ,IdentityColumn
                                    ,IdentitySeed
                                    ,IdentityIncrement
                                    ,IsCharColumn
                                    ,IsComputed)

    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName_1,@schema=@schema_1    

    INSERT INTO @ShowFields_2( DatabaseName
                                    ,TableOwner
                                    ,TableName
                                    ,FieldName
                                    ,ColumnPosition
                                    ,ColumnDefaultValue
                                    ,ColumnDefaultName
                                    ,IsNullable
                                    ,DataType
                                    ,MaxLength
                                    ,NumericPrecision
                                    ,NumericScale
                                    ,DomainName
                                    ,FieldListingName
                                    ,FieldDefinition
                                    ,IdentityColumn
                                    ,IdentitySeed
                                    ,IdentityIncrement
                                    ,IsCharColumn
                                    ,IsComputed)

    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @TableName=@TableName_2,@schema=@schema_2            


            ----------------------------------


    INSERT INTO @Definition(state,FieldValue_1)
    SELECT t2.c,t.FieldName + ' ' + 
        --CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) +CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END 

        CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + 
            CASe WHEN IsNullable = 1 THEN ' NULL ' 
            ELSE ' NOT NULL ' 
            END 
        ELSE 
            case when IsComputed is null then
                UPPER(DataType) +
                CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' 
                ELSE 
                    CASE WHEN DataType = 'numeric' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                    ELSE
                        CASE WHEN DataType = 'decimal' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                        ELSE '' 
                        end  
                    end 
                END +
                CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' 
                ELSE '' 
                END +
                CASE WHEN IsNullable = 1 THEN ' NULL ' 
                ELSE ' NOT NULL ' 
                END +
                CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + replace(ColumnDefaultName,@TableName_1,@TableName_2) + '] DEFAULT' + UPPER(ColumnDefaultValue) 
                ELSE '' 
                END 
            else
                ' as '+IsComputed+' '
            end
        END + 
        --, per add, ; per alter
        CASE WHEN t2.c = 1 THEN ',' 
        ELSE ';' 
        END 

    from
    (
        select *
        FROM    
        (       --escludo la ColumnPosition xkè:
                -- c1, c2, c3
                -- cancello c2 e aggiungo c4 quindi
                -- c1, c3, c4
                -- c2 è in posizione 2
                -- se considero anche la ColumnPosition c3 sarebbe da cancellare e ricreare, e perderei i dati
                -- in qsto modo mantengo la stessa colonna che con la drop delle altre si rimette al suo posto
                -- ATTENZIONE.!!.
                -- se cancello c2 e poi lo ricreo (passa da posizione 2 a posizione 3) nn percepisco il cambiamento
                select COUNT(*)c,MAX(TableOwner) s,MAX(TableName) t,MAX(ColumnPosition)p
                        ,FieldName,ColumnDefaultValue,ColumnDefaultName,IsNullable,DataType
                        ,MaxLength,NumericPrecision,NumericScale,DomainName,FieldListingName,FieldDefinition
                        ,IdentityColumn,IdentitySeed,IdentityIncrement,IsCharColumn,IsComputed
                from
                (
                    select * from @ShowFields_1 union all
                    select * from @ShowFields_2
                    --order by TableOwner,TableName,ColumnPosition
                )t
                group by FieldName,ColumnDefaultValue,ColumnDefaultName,IsNullable,DataType
                        ,MaxLength,NumericPrecision,NumericScale,DomainName,FieldListingName,FieldDefinition
                        ,IdentityColumn,IdentitySeed,IdentityIncrement,IsCharColumn,IsComputed
         )t
         where s=@schema_1 and t=@TableName_1 and c=1
     )t 
     --distinguo le colonne columi(t2.c=2 -> alter column; t2.c=1 -> add column)
     join
     (
         select FieldName,COUNT(*)c from
         (
            select FieldName from @ShowFields_1 union all
            select FieldName from @ShowFields_2
         )a
         group by FieldName 
     )t2 on t.FieldName=t2.FieldName
     order by s,t,p--,ColumnPosition

    INSERT INTO @Definition(FieldValue_2)
    SELECT  ' '+t.FieldName + ', '
    from
    (
        select *
        FROM    
        (       --escludo la ColumnPosition xkè:
                -- c1, c2, c3
                -- cancello c2 e aggiungo c4 quindi
                -- c1, c3, c4
                -- c2 è in posizione 2
                -- se considero anche la ColumnPosition c3 sarebbe da cancellare e ricreare, e perderei i dati
                -- in qsto modo mantengo la stessa colonna che con la drop delle altre si rimette al suo posto
                -- ATTENZIONE.!!.
                -- se cancello c2 e poi lo ricreo (passa da posizione 2 a posizione 3) nn percepisco il cambiamento
                select COUNT(*)c,MAX(TableOwner) s,MAX(TableName) t--,MAX(ColumnPosition)p
                        ,FieldName,ColumnDefaultValue,ColumnDefaultName,IsNullable,DataType
                        ,MaxLength,NumericPrecision,NumericScale,DomainName,FieldListingName,FieldDefinition
                        ,IdentityColumn,IdentitySeed,IdentityIncrement,IsCharColumn,IsComputed
                from
                (
                    select * from @ShowFields_1 union all
                    select * from @ShowFields_2
                    --order by TableOwner,TableName,ColumnPosition
                )t
                group by FieldName,ColumnDefaultValue,ColumnDefaultName,IsNullable,DataType
                        ,MaxLength,NumericPrecision,NumericScale,DomainName,FieldListingName,FieldDefinition
                        ,IdentityColumn,IdentitySeed,IdentityIncrement,IsCharColumn,IsComputed
         )t
         where s=@schema_2 and t=@TableName_2 and c=1
     )t 
     --distinguo le colonne columi tra le tabelle(t2.c=2 -> alter column; t2.c=1 -> add column)
     join
     (
         select FieldName,COUNT(*)c from
         (
            select FieldName from @ShowFields_1 union all
            select FieldName from @ShowFields_2
         )a
         group by FieldName
         having COUNT(*)=1  
     )t2 on t.FieldName=t2.FieldName
     order by s,t--,ColumnPosition

    --state=1 -> add
    --state=2 -> alter
    set @t0=case when (select COUNT(FieldValue_1) from @Definition where state=1)>0 then (select ' alter table '+@Schema_2+'.'+@TableName_2+' add '+(replace((select replace((SELECT SUBSTRING(FieldValue_1,0,LEN(FieldValue_1)+1) FROM @Definition where state=1 FOR XML PATH('')),'</FieldValue_1>','')),'<FieldValue_1>',''))) else '' end
    set @t1=case when (select COUNT(FieldValue_1) from @Definition where state=2)>0 then (select (replace((select replace((SELECT ' alter table '+@Schema_2+'.'+@TableName_2+' alter column '+SUBSTRING(FieldValue_1,0,LEN(FieldValue_1)+1) FROM @Definition where state=2 FOR XML PATH('')),'</FieldValue_1>','')),'<FieldValue_1>',''))) else '' end
    set @t2=case when (select COUNT(FieldValue_2) from @Definition)>0 then (select ' alter table '+@Schema_2+'.'+@TableName_2+' drop column '+(replace((select replace((SELECT SUBSTRING(FieldValue_2,0,LEN(FieldValue_2)+1) FROM @Definition FOR XML PATH('')),'</FieldValue_2>','')),'<FieldValue_2>',''))) else '' end

    --prima la drop, poi la add
    set @script_drop +=' '+ substring(@t2,0,len(@t2))
    set @script_add  =substring(@t0,0,len(@t0)) + ' ' + substring(@t1,0,len(@t1))+ ' ' +@script_add

    set @script=@script_drop+@script_add


END try
-- ##############################################################################################################################################################################
BEGIN CATCH        
    BEGIN

            PRINT 'ErrorNumber               : ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX))
            PRINT 'ErrorSeverity             : ' + CAST(ERROR_SEVERITY() AS NVARCHAR(MAX)) 
            PRINT 'ErrorState                : ' + CAST(ERROR_STATE() AS NVARCHAR(MAX)) 
            PRINT 'ErrorLine                 : ' + CAST(ERROR_LINE() AS NVARCHAR(MAX)) 
            PRINT 'ErrorMessage              : ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX))
            PRINT 
    END 
        set @script=''
    return -1
END CATCH   

执行它:

declare @s varchar(max)
exec [util_ScriptTable_Update]   'db','schema_source','table_source',1,1,'schema_dest','tab_dest',0,@s output
select @s