How to: Replicate Schema Changes (Replication Transact-SQL Programming)

If you make the following schema changes to a published article, they are propagated, by default, to Microsoft SQL Server Subscribers:

  • ALTER TABLE
  • ALTER VIEW
  • ALTER PROCEDURE
  • ALTER FUNCTION
  • ALTER TRIGGER

You can use replication stored procedures to specify whether these schema changes are replicated. The stored procedure that you use depends on the type of publication.

ms147331.note(zh-cn,SQL.90).gif注意:
ALTER TABLE DROP COLUMN is always replicated to all subscriptions whose partition contains the columns being dropped, regardless of the value of the @replicate_ddl parameter.

To create a snapshot or transactional publication that does not replicate schema changes

  1. At the Publisher on the publication database, execute sp_addpublication (Transact-SQL), specifying a value of 0 for @replicate_ddl. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To create a merge publication that does not replicate schema changes

  1. At the Publisher on the publication database, execute sp_addmergepublication (Transact-SQL), specifying a value of 0 for @replicate_ddl. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To temporarily disable replicating schema changes for a snapshot or transactional publication

  1. For a publication with replication of schema changes, execute sp_changepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 0 for @value.

  2. Execute the DDL command on the published object.

  3. (Optional) Re-enable replicating schema changes by executing sp_changepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 1 for @value.

To temporarily disable replicating schema changes for a merge publication

  1. For a publication with replication of schema changes, execute sp_changemergepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 0 for @value.

  2. Execute the DDL command on the published object.

  3. (Optional) Re-enable replicating schema changes by executing sp_changemergepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 1 for @value.

请参阅

其他资源

对发布数据库进行架构更改

帮助和信息

获取 SQL Server 2005 帮助