How to Apply Schema Changes on Publication Databases (Transact-SQL)

How to Apply Schema Changes on Publication Databases (Transact-SQL)

To add columns to an article

• Execute the sp_repladdcolumn stored procedure and set the following parameters.

  Parameter

Function @source_objectNames the table to which the column will be added. @columnNames the column to be added. @typetextDefines the column (data type information, default value, and so on.). In the SQL for the column definition, you must either specify a default value or allow NULL values.

For information about the syntax required to define the column, see ALTER TABLE.

@publication_to_addLists the names of the publications to which you will add the column; you can also use the values all or none. @force_invalidate_snapshotWhen set equal to 0, current snapshot with previous schema information is still available in case it is needed. This parameter affects only publications created with the immediate_sync option. @force_reinit_subscriptionWhen set equal to 1, schema changes commands will not be propagated to Subscribers. All subscriptions affected by the schema change will be reintialized except for nosync subscriptions, for which no action is taken.

To drop columns from an article

• Execute the sp_repldropcolumn stored procedure and set the following parameters.

  Parameter

Function @source_objectNames the table from which the column will be dropped. @columnNames the column to be dropped. @force_invalidate_snapshotWhen set equal to 0, current snapshot with previous schema information is still available in case it is needed. This parameter affects only publications created with the immediate_sync option. @force_reinit_subscriptionWhen set equal to 1, schema changes commands will not be propagated to Subscribers. All subscriptions affected by the schema change will be reintialized except for nosync subscriptions, for which no action is taken.