It is possible to add/remove columns to replicated tables for both transactional and merge replication as Paul says, however the 2 procedures provided cannot be used alone as they have some minor problems that need to be worked around. The following I have used on SQL 2000, have not tried on 2005.
The two SPs that come with SQL are sp_repladdcolumn and sp_repldropcolumn. sp_repldropcolumn works correctly, but you have to ensure you drop all dependent constraints from all subscribers and the publisher before using, otherwise it won't work. sp_repladdcolumn works well for transactional if you specify a static default, e.g. not a function like newid or getdate, for merge replication it does not work by itself at all, changes made to the added column never get replicated.
The limitation of functions is due to the sp_replxxxcolumn commands being replicated, so the functions fire on each server and each server gets unique values and each server is therefore different in these columns until the data is updated at some point in time.
So, to overcome these few quirks, I use the following code (Look for the TODO sections to specify your needs):
---------------------------------
/* For adding a column */
declare @tblname sysname
declare @colname sysname
declare @strSQL nvarchar(1000)
--TODO: Define the table you wish to add a column to and the new column name
set @tblname='customers'
set @colname='test'
--TODO: Change the column definition to what you want
exec sp_repladdcolumn @tblname, @colname, 'datetime not null default getdate()'
--This checks if the table is being used in merge replication and if so, ensures that the new column will actually receive updates (there is a bug in SQL 2000 sp_repladdcolumn, this should be automatic)
if (select count(*) from sysobjects where name='sysmergearticles' and type='U')>0
begin
if (select count(*) from sysmergearticles where name=@tblname)>0
exec sp_MSaddmergetriggers @tblname
end
--This will ensure if you have a default that is a function, e.g. newid(), getdate() that the value becomes the same on all subscribers. Without these lines, it will be different everywhere based on when the column was locally added.
set @strSQL='update [' + @tblname + '] set [' + @colname + '] = [' + @colname + ']'
exec (@strSQL)
---------------------------------
---------------------------------
/* For removing a column
TODO: If you have any constraints, run this on all subscribers first, apart from the last line.
Then run it completely on the publisher */
declare @tblname sysname
declare @colname sysname
declare @strSQL nvarchar(4000)
--TODO: Define the table you wish to add a column to and the new column name
set @tblname='customers'
set @colname='test'
--Remove all the constraints for this column
-- find the constraints and build a query
select @strSQL='alter table [' + @tblname + '] drop constraint [' + OBJ.name + ']' + char(13) + isnull(@strSQL,'') from sysconstraints as CON
inner join syscolumns as COL
on COL.id=CON.id and COL.colid=CON.colid
inner join sysobjects as OBJ
on OBJ.id=CON.constid
where COL.name=@colname
print @strSQL
exec (@strSQL)
--Only needed on the publisher, actually removes the column
exec sp_repldropcolumn @tblname, @colname
---------------------------------
Cheers,
Scott