Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list
here.
Goto
Definition or
MetaData sys.sp_repladdcolumn(
nvarchar @source_object,
nvarchar @column,
nvarchar @typetext,
nvarchar @publication_to_add,
int @from_agent,
nvarchar @schema_change_script,
bit @force_invalidate_snapshot,
bit @force_reinit_subscription)
create procedure sys.sp_repladdcolumn( @source_object nvarchar (358), @column sysname, @typetext nvarchar(3000), @publication_to_add nvarchar(4000) = 'all', @from_agent int = 0, @schema_change_script nvarchar(4000) = NULL, @force_invalidate_snapshot bit = 1, -- Force invalidate existing snapshot -- @force_reinit_subscription bit = 0 -- Force reinit subscription -- ) as set nocount on declare @objid int declare @pubid uniqueidentifier declare @retcode int declare @partitioned int declare @at_publisher bit declare @pubname sysname declare @artname sysname declare @colid int declare @tablename sysname declare @artid uniqueidentifier declare @tran_artid int declare @tran_pubid int declare @schematext nvarchar(4000) declare @schemaversion int declare @schematype int declare @schemaguid uniqueidentifier declare @conflict_table sysname declare @ins_conflict_proc nvarchar(258) declare @publisher sysname declare @qual_column nvarchar(258) declare @publisher_db sysname declare @command nvarchar(max) declare @sync_objid int declare @con_id int declare @owner sysname declare @dest_owner sysname declare @dest_object nvarchar(517) declare @merge_pub_object_bit int declare @is_for_merge bit -- 1 if merge is on AND the table is published in a merge publication AND the publication is in @publivation_to_add list declare @is_for_tran bit declare @not_for_merge int -- 0 if merge is on AND the table is published in a merge publication declare @not_for_tran int declare @dbname sysname declare @servername sysname declare @no_publication bit declare @no_schema_replication bit declare @qual_source_object nvarchar(517) declare @len int declare @prec int declare @scale int declare @typename nvarchar(270) declare @qual_dest_object nvarchar(517) declare @invalid_datatype bit declare @is_yukon_datatype bit declare @xtype int declare @max_length int declare @iscomputed bit declare @pass_to_heterogenious bit declare @merge_pub_markcolumn_bit int declare @schema_start_lsn binary(10) declare @schema_end_lsn binary(10) declare @nickname int declare @columns_in_partition int declare @artnick int declare @allow_initialize_from_backup bit declare @is_timestamp_column bit declare @is_identity_column bit declare @is_identity_column_not_for_replication bit ,@BinVar varbinary(128) declare @count int declare @DATATYPE_ID_UDT int declare @got_merge_admin_applock bit select @got_merge_admin_applock = 0 -- -- Security Check -- EXEC @retcode = sys.sp_MSreplcheck_publish IF @@ERROR <> 0 or @retcode <> 0 return (1) select @qual_column=QUOTENAME(@column) select @merge_pub_markcolumn_bit = 0x4000 select @objid = object_id(@source_object) -- check to see if this stored procedure is called via replication agent. if sessionproperty('replication_agent')<>1 select @from_agent = 0 else begin -- if table does not exists and the SP is called from replication agent, ignore the error and exit with success. select @from_agent = 1 if @objid is NULL return (0) end if @objid is NULL begin raiserror(14027, 16, -1, @source_object) return (1) end if exists (select * from sys.columns where object_id=@objid and name=@column) begin if @from_agent=0 begin raiserror(21255, 16, -1, @column, @source_object) return (1) end else return (0) end -- PARSENAME VARS declare @UnqualName sysname -- rightmost name node declare @QualName1 sysname -- END PARSENAME VARS select @UnqualName = PARSENAME(@source_object, 1) select @QualName1 = PARSENAME(@source_object, 2) if @UnqualName IS NULL return 1 if @QualName1 is not NULL select @owner = @QualName1 else select @owner=schema_name(schema_id) from sys.objects where object_id = @objid select @tablename = @UnqualName select @qual_source_object = QUOTENAME(@owner) + '.' + QUOTENAME(@tablename) select @no_schema_replication = 0 select @no_publication=0 select @servername=publishingservername() select @dbname=db_name() select @merge_pub_object_bit = 128 select @not_for_merge = 0 select @not_for_tran = 0 if exists (select * from sys.objects where name='sysmergearticles') begin -- the article can not be inside a republisher if @from_agent=0 and exists (select * from dbo.sysmergearticles where objid=@objid and pubid not in (select pubid from dbo.sysmergepublications where LOWER(publisher)=LOWER(@servername) and publisher_db=@dbname)) begin raiserror(21260, 16, -1, @dbname, @servername, @source_object) return (1) end end select @invalid_datatype=0 select @is_yukon_datatype=0 select @DATATYPE_ID_UDT = 240 -- system_type_id for UDT. -- signal to db ddl trigger to bail out EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=1 IF @@ERROR <> 0 or @retcode <> 0 RETURN(1) -- the transaction below is just an experiment to validate type text -- BEGIN TRAN SAVE TRANSACTION fake_move exec @retcode = sys.sp_MSunmarkreplinfo @object=@tablename, @owner=@owner if @retcode<>0 or @@ERROR<>0 begin goto CHECKING_DONE end declare @temp_id int declare @temp_colid int select @is_identity_column = 0 select @is_timestamp_column = 0 select @is_identity_column_not_for_replication = 0 exec ('alter table ' + @qual_source_object + ' add ' + @qual_column + ' ' + @typetext) select @temp_id = object_id(@qual_source_object) if @temp_id is NULL begin raiserror(21285, 16, -1, @column, @source_object) select @invalid_datatype = 1 GOTO CHECKING_DONE end select @pass_to_heterogenious=0, @temp_colid = column_id, @xtype=system_type_id, @iscomputed=is_computed, @max_length = max_length from sys.columns where object_id = @temp_id and name=@column if @iscomputed <> 1 and type_name(@xtype) <> 'timestamp' select @pass_to_heterogenious=1 if COLUMNPROPERTY(@temp_id, @column, 'IsIdentity') = 1 select @is_identity_column = 1 if type_name(@xtype) = 'timestamp' select @is_timestamp_column = 1 if @is_identity_column = 1 and COLUMNPROPERTY(@temp_id, @column, 'IsIdNotForRepl') = 1 select @is_identity_column_not_for_replication = 1 if COLUMNPROPERTY(@temp_id, @column, 'IsIdentity') = 0 and not exists (select * from sys.columns where object_id = @temp_id and name=@column and is_nullable=1) and not exists (select * from sysconstraints where id=@temp_id and colid=@temp_colid and OBJECTPROPERTY ( constid , 'IsDefaultCnst' ) = 1) and @iscomputed <> 1 and type_name(@xtype) <> 'timestamp' -- not computed column nor timestamp column begin select @invalid_datatype = 1 end -- Check for yukon data type. if (@xtype = @DATATYPE_ID_UDT) OR ((@max_length = -1) and type_name(@xtype) in ('xml','varchar','nvarchar','varbinary')) begin select @is_yukon_datatype = 1 end CHECKING_DONE: ROLLBACK TRANSACTION fake_move COMMIT TRAN if @invalid_datatype = 1 begin raiserror(21285, 16, -1, @column, @source_object) return (1) end select @is_for_merge = 0 select @is_for_tran = 0 if not exists (select * from sys.objects where name='sysmergepublications') and not exists (select * from sys.objects where name='syspublications') begin raiserror(21230, 16, -1) return (1) end create table #tmp_table (name sysname collate database_default) if LOWER(@publication_to_add)='all' or LOWER(@publication_to_add)='[all]' begin select @publication_to_add = '%' end else begin if charindex(',', @publication_to_add)<>0 -- publication list begin declare @tmp_publist nvarchar(4000) declare @total int declare @invalid_pub sysname select @invalid_pub = NULL select @tmp_publist = @publication_to_add while @tmp_publist <> '' begin select @tmp_publist = LTRIM(@tmp_publist) select @total=len(@tmp_publist) select @len=charindex(',', @tmp_publist) if @len=0 begin insert #tmp_table values(@tmp_publist) select @tmp_publist='' end else begin insert #tmp_table values(substring(@tmp_publist, 1, @len-1)) select @tmp_publist = right(@tmp_publist, @total-@len) end end if exists (select * from sys.objects where name='sysmergepublications') and exists (select * from sys.objects where name='syspublications') select @invalid_pub=name from #tmp_table where name not in (select ('[' + name + ']') from dbo.sysmergepublications) and name not in (select ('[' + name + ']') from syspublications) else if exists (select * from sys.objects where name='sysmergepublications') select @invalid_pub=name from #tmp_table where name not in (select ('[' + name + ']') from dbo.sysmergepublications) else if exists (select * from sys.objects where name='syspublications') select @invalid_pub=name from #tmp_table where name not in (select ('[' + name + ']') from syspublications) if @invalid_pub is not NULL and @from_agent=0 begin raiserror(21274, 16, -1, @invalid_pub) return (1) end end else begin -- single publication insert #tmp_table values(@publication_to_add) insert #tmp_table values('[' + @publication_to_add + ']') end end -- if in both merge and tran publication if exists (select * from sys.objects where name='sysmergepublications') and exists (select * from sys.objects where name='syspublications') begin if not exists (select * from syspublications where name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table))) and not exists (select * from dbo.sysmergepublications where name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table))) select @no_publication = 1 if not exists (select * from sysarticles where objid=@objid and pubid in (select pubid from syspublications where repl_freq = 0 and (name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table))))) and not exists (select * from dbo.sysmergearticles where objid=@objid and pubid in (select pubid from dbo.sysmergepublications where name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table)))) select @no_schema_replication=1 end -- if a merge publication else if exists (select * from sys.objects where name='sysmergepublications') begin if not exists (select * from dbo.sysmergepublications where name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table))) select @no_publication = 1 if not exists (select * from dbo.sysmergearticles where objid=@objid and pubid in (select pubid from dbo.sysmergepublications where name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table)))) select @no_schema_replication = 1 end -- if tran publication else if exists (select * from sys.objects where name='syspublications') begin if not exists (select * from syspublications where name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table))) select @no_publication = 1 if not exists (select * from sysarticles where objid=@objid and pubid in (select pubid from syspublications where repl_freq = 0 and (name like @publication_to_add or (('[' + name + ']') in (select name from #tmp_table))))) select @no_schema_replication =1 end if @no_publication=1 and @publication_to_add <> '%' and LOWER(@publication_to_add)<>'none' and LOWER(@publication_to_add)<>'[none]' begin raiserror(21200, 16, -1, @publication_to_add) return (1) end if @no_schema_replication=1 and LOWER(@publication_to_add)<>'none' and LOWER(@publication_to_add)<>'[none]' begin if @publication_to_add = '%' begin raiserror(21246, 16, -1, @source_object) return (1) end else begin raiserror(21245, 16, -1, @source_object, @publication_to_add) return (1) end end select @at_publisher = 0 if exists (select * from sys.objects where name = 'sysarticles') begin if exists (select a.objid from sysarticles a, syspublications p where a.objid=@objid and p.pubid = a.pubid and (p.name like @publication_to_add or charindex('[' + p.name + ']', @publication_to_add)>0)) begin select @is_for_tran = 1 end end if exists (select * from sys.objects where name = 'sysmergearticles') begin if exists (select a.objid from sysmergearticles a, sysmergepublications p where a.objid=@objid and p.pubid = a.pubid and (p.name like @publication_to_add or charindex('[' + p.name + ']', @publication_to_add)>0)) begin select @is_for_merge = 1 end end else select @is_for_merge = 0 -- Cannot add yukon specific datatypes using repladdcolumn when backward comp level is less than 90 -- for merge. For tran we never adding these datatypes using sp_repladdcolumn. Use alter table for tran. if (@is_for_merge = 1) and (@is_yukon_datatype = 1) begin if ((select min(backward_comp_level) from dbo.sysmergepublications where pubid in (select pubid from dbo.sysmergearticles where objid=@objid)) < 90) begin raiserror(25014, 16, -1) return (1) end end else if (@is_for_tran = 1) and (@is_yukon_datatype = 1) begin raiserror(25014, 16, -1) return (1) end if (@is_for_tran =1 and @force_reinit_subscription = 0) begin if (@is_timestamp_column = 1 ) begin raiserror(21380, 16, -1) return (1) end if (@is_identity_column = 1 and @is_identity_column_not_for_replication <> 1) begin begin raiserror(21810, 16, -1) return (1) end end if exists (SELECT P.pubid FROM dbo.syspublications P, dbo.sysarticles A, dbo.syssubscriptions S WHERE A.objid = @objid and A.pubid = P.pubid and A.artid = S.artid and (P.allow_sync_tran = 1 or P.allow_queued_tran = 1) and S.status = 2 -- active and S.sync_type != 2 -- not 'none' and S.update_mode != 0 ) -- not 'read only' begin raiserror(21381, 16, -1, @tablename) return (1) end end begin TRAN save tran sp_repladdcolumn -- if not exists (select * from tempdb.sys.objects where name = '##MS_internal_old_style_DDL_support_being_used') create table ##MS_internal_old_style_DDL_support_being_used(db_name sysname, objid int) insert ##MS_internal_old_style_DDL_support_being_used(db_name, objid) values(db_name(), @objid) if @@ERROR<>0 goto FAILURE -- if not exists (select * from sys.objects where name='sysmergearticles') select @not_for_merge = 1 else if not exists (select * from dbo.sysmergearticles where objid=@objid) select @not_for_merge =1 if not exists (select * from sys.objects where name='sysarticles') select @not_for_tran = 1 else if not exists (select * from dbo.sysarticles where objid=@objid) select @not_for_tran =1 -- Merge only supports a max of 246 columns. Check and error out if we already have -- 246 columns that are replicating and we are trying to add another one. if @is_for_merge = 1 begin select @count = count(*) from sys.columns where object_id=@objid and is_computed<>1 and system_type_id <> type_id('timestamp') and is_merge_published = 1 IF @count > 245 BEGIN RAISERROR(25006, 16, -1, @qual_source_object, 245) goto FAILURE END end -- -- -- -- -- -- *tran alter table part if @is_for_tran = 1 begin if not exists (select * from sys.columns where name=@column and object_id =@objid) begin exec @retcode = sys.sp_MSprep_exclusive @qual_source_object if @@ERROR<>0 or @retcode <> 0 goto FAILURE exec @retcode = sp_replincrementlsn @schema_start_lsn OUTPUT if @@ERROR<>0 or @retcode <> 0 goto FAILURE exec ('alter table '+ @qual_source_object + ' add ' + @qual_column + ' ' + @typetext ) if @@ERROR<>0 goto FAILURE end end -- if tran is enabled, but merge is not, sp_repladdcolumn with none pub list should just add the column else if @not_for_merge = 1 and @not_for_tran = 0 and (LOWER(@publication_to_add) = 'none' or LOWER(@publication_to_add) = '[none]') begin exec ('alter table '+ @qual_source_object + ' add ' + @qual_column + ' ' + @typetext ) if @@ERROR<>0 goto FAILURE end -- -- -- -- -- -- *merge alter table part if @is_for_merge=1 begin -- obtain the snapshot/DDL/admin proc applock to avoid having contention with snapshot -- Attempt to get the lock with no wait exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait @lockowner = N'Transaction' if @retcode<>0 or @@error<>0 goto FAILURE select @got_merge_admin_applock = 1 exec @retcode = sys.sp_MSunmarkreplinfo @object=@tablename, @owner=@owner if @retcode<>0 or @@ERROR<>0 goto FAILURE if not exists (select * from sys.columns where name=@column and object_id =@objid) begin exec ('alter table '+ @qual_source_object + ' add ' + @qual_column + ' ' + @typetext ) if @@ERROR<>0 goto FAILURE end end -- cannot add @not_for_tran = 1 as the case for "if none publist and both merge/tran are on" else if @not_for_merge = 0 and (LOWER(@publication_to_add) = 'none' or LOWER(@publication_to_add) = '[none]') begin if not exists (select * from sys.columns where name=@column and object_id =@objid) begin exec ('alter table '+ @qual_source_object + ' add ' + @qual_column + ' ' + @typetext ) if @@ERROR<>0 goto FAILURE end end -- Now for real work if publist is not empty if @is_for_merge=1 begin if not exists (select * from sys.columns where name=@column and object_id =@objid) begin exec ('alter table '+ @qual_source_object + ' add ' + @qual_column + ' ' + @typetext ) if @@ERROR<>0 goto FAILURE end -- -- whether the column is added to this publication, the articles whose underlying table contains -- a new column will be regareded as vertically partitioned. -- update dbo.sysmergearticles set vertical_partition=1 where objid=@objid if @@ERROR<>0 goto FAILURE -- -- Heterogenious subscriber are not to receive computed column or timestamp columns -- declare #nosnapshot CURSOR LOCAL FAST_FORWARD for select p.name, a.name, a.pubid from dbo.sysmergepublications p, dbo.sysmergearticles a where p.pubid=a.pubid and a.objid=@objid and p.snapshot_ready=0 and (p.name like @publication_to_add or charindex('[' + p.name + ']', @publication_to_add)>0) and (p.sync_mode=0 or @pass_to_heterogenious=1) open #nosnapshot fetch #nosnapshot into @pubname, @artname, @pubid while (@@fetch_status<>-1) begin -- Indicate that this is an internal caller of sp_mergearticlecolumn EXEC @retcode = sys.sp_MSsetcontext_internalcaller @onoff=1 IF @@ERROR <> 0 or @retcode <> 0 goto ERROR_EXIT exec @retcode = sys.sp_mergearticlecolumn @pubname, @artname, @column, 'add', 'true' if @retcode <>0 or @@ERROR<>0 goto ERROR_EXIT -- Turn off indication that this is an internal caller of sp_mergearticlecolumn EXEC @retcode = sys.sp_MSsetcontext_internalcaller @onoff=0 IF @@ERROR <> 0 or @retcode <> 0 goto ERROR_EXIT fetch #nosnapshot into @pubname, @artname, @pubid end CLOSE #nosnapshot DEALLOCATE #nosnapshot -- -- Handle those cases where snapshot is already ran and new schema change needs to be added -- ; Heterogenious subscriber are not to receive computed column or timestamp columns -- declare #addcolumn CURSOR LOCAL FAST_FORWARD for select pubid, name, sync_objid, artid, nickname, conflict_table, ins_conflict_proc, destination_owner from dbo.sysmergearticles where objid=@objid and pubid in (select pubid from dbo.sysmergepublications where snapshot_ready>0 and (sync_mode=0 or @pass_to_heterogenious=1)) open #addcolumn fetch #addcolumn into @pubid, @artname, @sync_objid, @artid, @artnick, @conflict_table, @ins_conflict_proc, @dest_owner while (@@fetch_status <> -1) BEGIN if @dest_owner is not NULL select @dest_object = QUOTENAME(@dest_owner) + '.' + QUOTENAME(@tablename) else select @dest_object = QUOTENAME(@tablename) select @pubname=name, @publisher=publisher, @publisher_db=publisher_db from dbo.sysmergepublications where pubid=@pubid if @conflict_table is not NULL begin select @con_id = object_id(quotename(@conflict_table)) if @con_id is NULL begin raiserror(21286, 16, -1, @conflict_table) GOTO INNER_FAIL end if not exists (select name from sys.columns where name=@column and object_id=@con_id) and (@pubname like @publication_to_add or charindex('[' + @pubname + ']', @publication_to_add)>0) begin select @typename = type_name(system_type_id), @len = max_length, @prec = precision, @scale = scale, @iscomputed=is_computed, @xtype=system_type_id from sys.columns where name=@column and object_id=@objid -- we want to column property of 'computed' or 'timestamp' of the newly added column if @iscomputed <> 1 and type_name(@xtype) <> 'timestamp' begin if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes select @len = @len/2 exec @retcode = sys.sp_MSRecontructType @typename out, @len, @prec, @scale if @@error<>0 OR @retcode <>0 GOTO INNER_FAIL -- doing so instead of using @typetext as we once did is that we do not want any constraint or identity properties -- defined on conflict tables. Also it is safe to make the column NULLable in conflict tables. select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' add ' + @qual_column + ' ' + @typename + ' NULL ' end else -- continue to use typetext when adding computed/timestamp column to the conflict tables. begin select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' add ' + @qual_column + ' ' + @typetext end exec (@command) if @@ERROR<>0 goto INNER_FAIL end end -- -- Force the re-generation of conflict insert table cause the schema is changed for sure -- if @ins_conflict_proc is not NULL and object_id(@ins_conflict_proc) is not NULL and (@pubname like @publication_to_add or charindex('[' + @pubname + ']', @publication_to_add)>0) begin select @ins_conflict_proc = QUOTENAME(@ins_conflict_proc) exec ('drop procedure ' + @ins_conflict_proc) if @@ERROR<>0 goto INNER_FAIL update dbo.sysmergearticles set ins_conflict_proc=NULL where pubid=@pubid and artid=@artid if @@ERROR<>0 goto INNER_FAIL end -- -- Force the regeneration of publication view for all publications that publish the current table. -- update dbo.sysmergearticles set vertical_partition=1 where artid=@artid and pubid=@pubid if @@ERROR<>0 goto INNER_FAIL select @at_publisher=0 if @publisher=publishingservername() and @publisher_db=db_name() begin select @at_publisher=1 if @pubname like @publication_to_add or charindex('[' + @pubname + ']', @publication_to_add)>0 begin select @schematext = 'exec sp_repladdcolumn ''' + replace(@dest_object, '''', '''''') + ''',''' + replace(@column, '''', '''''') + ''','+ QUOTENAME(@typetext) + ', ''' + replace(@publication_to_add, '''', '''''') + ''', 1' select @schemaversion = schemaversion from dbo.sysmergeschemachange if (@schemaversion is NULL) set @schemaversion = 1 else select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange set @schemaguid = newid() set @schematype = 11 -- alter table -- -- Indicate that this is an internal caller of sp_mergearticlecolumn EXEC @retcode = sys.sp_MSsetcontext_internalcaller @onoff=1 IF @@ERROR <> 0 or @retcode <> 0 goto INNER_FAIL exec @retcode = sys.sp_mergearticlecolumn @pubname, @artname, @column, 'add','true' if @retcode<>0 or @@ERROR<>0 goto INNER_FAIL -- need to deallocate cursor for this loop. -- Turn off indication that this is an internal caller of sp_mergearticlecolumn EXEC @retcode = sys.sp_MSsetcontext_internalcaller @onoff=0 IF @@ERROR <> 0 or @retcode <> 0 goto INNER_FAIL exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext if @@ERROR<>0 or @retcode<>0 goto INNER_FAIL -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- prepare parsing event create table #SSCE_parsedschemacmds( id int identity, DDLsubtype sysname, TableOwner sysname, TableName sysname, ColumnName sysname NULL, ColumnAttr sysname NULL ) select @schematext = 'alter table ' + @dest_object + ' add ' + QUOTENAME(@column) + ' ' + @typetext insert #SSCE_parsedschemacmds(DDLsubtype, TableOwner, TableName, ColumnName, ColumnAttr) exec sys.sp_replddlparser @ddlCmd=@schematext if @@ERROR <> 0 begin DROP TABLE #SSCE_parsedschemacmds goto INNER_FAIL end -- If the sp_repladdcolumn @typetext contains foreign key constraints, use the sp_MSNonSQLDDL to post them as separate schema changes -- if exists( select * from #SSCE_parsedschemacmds psc where psc.DDLsubtype = N'ADDREFERENCE') begin -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Fix up foreign key constraint names -- declare @colname sysname declare @typeid varchar(10) declare @modified_date datetime declare @row_id int DECLARE #fkcursor CURSOR LOCAL FAST_FORWARD for select name, type, modify_date from sys.foreign_keys where parent_object_id = @objid order by modify_date desc open #fkcursor fetch #fkcursor into @colname, @typeid, @modified_date while @@fetch_status <> -1 begin set @row_id = NULL select top 1 @row_id = id from #SSCE_parsedschemacmds psc where psc.DDLsubtype = N'ADDREFERENCE' and len(isnull(ColumnName, '')) = 0 order by id desc if @row_id is null begin break end else begin update #SSCE_parsedschemacmds set ColumnName = @colname where id = @row_id if @@error > 0 goto INNER_FAIL end fetch #fkcursor into @colname, @typeid, @modified_date end close #fkcursor deallocate #fkcursor declare @statement_id int declare @schemasubtype int declare @subtype sysname declare @parsed_columnname sysname declare #alter_table_cursor CURSOR LOCAL FAST_FORWARD FOR select DISTINCT id, DDLsubtype, ColumnName from #SSCE_parsedschemacmds order by id ASC for read only open #alter_table_cursor fetch #alter_table_cursor into @statement_id, @subtype, @parsed_columnname while (@@fetch_status <> -1) begin if @subtype=N'ADDCOLUMN' select @schemasubtype = 1 else if @subtype = N'ADDPRIMARYKEY' select @schemasubtype = 4 else if @subtype = N'ADDUNIQUE' select @schemasubtype = 5 else if @subtype = N'ADDREFERENCE' select @schemasubtype = 6 else if @subtype = N'ADDDEFAULT' select @schemasubtype = 8 else select @schemasubtype = 0 -- dummy row exec @retcode = sys.sp_MSNonSQLDDL @qual_source_object, @pubid, @parsed_columnname, @schemasubtype -- add column if @@ERROR<>0 or @retcode<>0 begin DROP TABLE #SSCE_parsedschemacmds close #alter_table_cursor deallocate #alter_table_cursor goto INNER_FAIL end fetch #alter_table_cursor into @statement_id, @subtype, @parsed_columnname end -- while-loop over #alter_table_cursor close #alter_table_cursor deallocate #alter_table_cursor end else begin -- insert a schema change for non SQL subscribers select @schematext = 'alter table ' + @dest_object + ' add ' + @column + ' ' + @typetext select @schemaversion = @schemaversion + 1 set @schematype = 13 -- for non SQL subscribers use only set @schemaguid = newid() exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext if @@ERROR<>0 or @retcode<>0 begin DROP TABLE #SSCE_parsedschemacmds goto INNER_FAIL end end DROP TABLE #SSCE_parsedschemacmds end end if @at_publisher=1 begin exec @retcode = sys.sp_MSpublicationview @publication = @pubname, @force_flag = 2, @articlename = @artname if @retcode<>0 or @@ERROR<>0 goto INNER_FAIL end select @columns_in_partition=count(*) from sys.columns where object_id = @sync_objid if @columns_in_partition>246 begin raiserror(21759, 16, 1,@column, @source_object) goto INNER_FAIL end -- only 246 colv entries are allowed if article belongs to a publication with Shiloh compat level if ((select min(backward_comp_level) from dbo.sysmergepublications where pubid in (select pubid from dbo.sysmergearticles where objid=@objid)) < 90) and sys.fn_cColvEntries_80(@pubid, @artnick) >= 246 begin raiserror(21523,16,1,'@column','@source_object', 246) goto INNER_FAIL end exec @retcode = sys.sp_MSdroparticletriggers @tablename, @owner if @retcode<>0 or @@ERROR<>0 goto INNER_FAIL exec @retcode = sys.sp_MSaddmergetriggers @qual_source_object if @retcode<>0 or @@ERROR<>0 goto INNER_FAIL -- -- Force the regeneration of merge procs for all publications that publish the current table -- if @at_publisher=1 exec @retcode = sys.sp_MSsetartprocs @publication=@pubname, @article=@artname, @force_flag=0, @pubid=@pubid else exec @retcode = sys.sp_MSmakearticleprocs @pubid, @artid if @retcode<>0 or @@ERROR<>0 goto INNER_FAIL -- -- passing in one more parameter to sp_MSgetconflictinsertproc such that there is -- no select statement at the end of that SP as needed else where. The default is 1. -- exec @retcode = sys.sp_MSgetconflictinsertproc @artid, @pubid, 0, 1 if @retcode<>0 or @@ERROR<>0 goto INNER_FAIL fetch #addcolumn into @pubid, @artname, @sync_objid, @artid, @artnick, @conflict_table, @ins_conflict_proc, @dest_owner END close #addcolumn deallocate #addcolumn end if @is_for_tran = 1 begin declare #trancolumn CURSOR LOCAL FAST_FORWARD for select a.artid, p.name, p.allow_initialize_from_backup from sysarticles a, syspublications p where a.objid=@objid and p.pubid = a.pubid and (p.name like @publication_to_add or charindex('[' + p.name + ']', @publication_to_add)>0) open #trancolumn fetch #trancolumn into @tran_artid, @pubname, @allow_initialize_from_backup while (@@fetch_status <> -1) BEGIN -- Error out if the current publication allows backup subscription -- and the column being added is a timestamp column (restriction is temporarily lifted) -- if @allow_initialize_from_backup = 1 and @is_timestamp_column = 1 -- begin -- raiserror(18797, 16, -1) -- select @retcode = 1 -- goto DROPTRAN -- end select @artname = name, @tran_pubid = pubid, @owner = dest_owner, @tablename = dest_table from sysarticles where artid=@tran_artid exec @retcode = sys.sp_articlecolumn @publication = @pubname, @article = @artname, @column = @column, @operation = 'add', @change_active = 2 ,@force_invalidate_snapshot = 1 ,@force_reinit_subscription = 1 ,@internal = 1 if @retcode <>0 or @@ERROR<>0 goto DROPTRAN if(@force_reinit_subscription = 0) begin if(@owner is not NULL) and (len(@owner) > 0) select @qual_dest_object = QUOTENAME(@owner) + N'.' else select @qual_dest_object = N'' if(@tablename is not null) and (len(@tablename) > 0) select @qual_dest_object = @qual_dest_object + QUOTENAME(@tablename) else select @qual_dest_object = @qual_source_object exec @retcode = sys.sp_MSrepl_schema @pubname = @pubname ,@artid = @tran_artid ,@qual_source_object = @qual_dest_object ,@column = @column ,@operation = 0 -- 0 is add, 1 is delete ,@typetext = @typetext ,@schema_change_script = @schema_change_script if @retcode <>0 or @@ERROR<>0 goto DROPTRAN end exec @retcode = sys.sp_MSreinit_article @publication = @pubname ,@article = @artname ,@need_new_snapshot = @force_invalidate_snapshot ,@need_reinit_subscription = @force_reinit_subscription ,@force_invalidate_snapshot = @force_invalidate_snapshot ,@force_reinit_subscription = @force_reinit_subscription if @retcode <>0 or @@ERROR<>0 goto DROPTRAN if @allow_initialize_from_backup = 1 begin -- If the column being added is an identity column that is -- not marked 'not for replication', do so now. Re-acquire -- schema mod lock to make the code more resilient to changes -- although this must have been done inside sp_Mrepl_schema if @is_identity_column = 1 and @is_identity_column_not_for_replication <> 1 begin exec %%Object(MultiName = @qual_source_object).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0) -- exec %%Object(MultiName = @qual_source_object).LockExclusiveMatchID(ID = @objid) if @@error <> 0 begin select @retcode = 1 goto DROPTRAN end exec %%ColumnEx(ObjectID = @objid, Name = @column).SetIdentityNotForRepl(Value = 1) if @@error <> 0 begin select @retcode = 1 goto DROPTRAN end end -- Note that the min publication autonosync lsn is -- incremented in sp_articlecolumn end fetch #trancolumn into @tran_artid, @pubname, @allow_initialize_from_backup END close #trancolumn deallocate #trancolumn exec @retcode = sp_replincrementlsn @schema_end_lsn OUTPUT if @@ERROR<>0 or @retcode <> 0 goto FAILURE if (@schema_start_lsn is not null) and (@schema_end_lsn is not null) begin insert systranschemas (tabid, startlsn, endlsn, typeid) values (@objid, @schema_start_lsn, @schema_end_lsn, 51) if @@ERROR<>0 goto FAILURE exec @retcode = sys.sp_replflush @objid, @schema_start_lsn, @schema_end_lsn if @@ERROR<>0 or @retcode <> 0 goto FAILURE end else goto FAILURE end -- Get back to its original replication state -- if @is_for_merge=1 begin exec @retcode = sys.sp_MSmarkreplinfo @tablename, @owner if @retcode<>0 or @@ERROR<>0 goto FAILURE end -- end of signal EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0 if @got_merge_admin_applock=1 exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction' COMMIT TRAN -- delete ##MS_internal_old_style_DDL_support_being_used where db_name=db_name() and objid= @objid return (0) INNER_FAIL: CLOSE #addcolumn DEALLOCATE #addcolumn GOTO FAILURE ERROR_EXIT: CLOSE #nosnapshot DEALLOCATE #nosnapshot GOTO FAILURE DROPTRAN: CLOSE #trancolumn DEALLOCATE #trancolumn FAILURE: if @got_merge_admin_applock=1 exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction' rollback tran sp_repladdcolumn commit tran raiserror(21285, 16, -1, @column, @source_object) -- end of signal EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0 return (1)
