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.
.gif "ms147331.note(zh-tw,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
- 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
- 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
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.
Execute the DDL command on the published object.
(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
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.
Execute the DDL command on the published object.
(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.