Adding Columns to Replicated Tables

[Sunite]

Adding Columns to Replicated Tables



Q
We often add columns to replicated tables. How can we add a column without having to reinitialize the entire publication?



A

In SQL Server 2000, you can use the sp_repladdcolumn stored procedure to add a column to a replicated table without reinitializing the entire publication because the stored procedure automatically adds the column at the subscriber. For example, if the authors table in the pubs database has already been published, you can add the newcol integer column to that table by executing the following stored procedure:

sp_repladdcolumn @source_object = 'authors'
, @column = 'newcol'
, @typetext = 'INT'
, @publication_to_add = '<name of publication authors is included in>'

Note that you can use the sp_repladdcolumn stored procedure to add only new columns to a replicated table; you can't use it to manage a table's existing columns. To drop existing columns from a published table, you can use the sp_repldropcolumn stored procedure.