Recently I wrote a post which having a script to get articles details added in replication for all publications. Today I am sharing a script to add articles in the existing publication of transactional replication, which we can also add through user interface of publication property.
Here I am sharing a query to add tables, views, stored procedures, indexed views and functions.
1. Table :
1. Table :
USE PublisherDB GO EXEC sp_addarticle @publication = N'PublicationName', @article = N'TableName', @source_owner = N'SchemaName', @source_object = N'TableName', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000080350DF, @identityrangemanagementoption = N'manual', @destination_table = N'TableName', @destination_owner = N'SchemaName', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]', @force_invalidate_snapshot = 1 GO2. View :
USE PublisherDB GO EXEC sp_addarticle @publication = N'PublicationName', @article = N'ViewName', @source_owner = N'schemaName', @source_object = N'ViewName', @type = N'view schema only', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'ViewName', @destination_owner = N'schemaName', @status = 16 GO3. Stored Procedure :
USE PublisherDB GO EXEC sp_addarticle @publication = N'PublicationName', @article = N'ProcedureName', @source_owner = N'SchemaName', @source_object = N'ProcedureName', @type = N'proc schema only', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'ProcedureName', @destination_owner = N'SchemaName', @status = 16 GO4. Indexed View:
USE PublisherDB GO EXEC sp_addarticle @publication = N'PublicationName', @article = N'Indexed View Name', @source_owner = N'SchemaName', @source_object = N'Indexed View Name', @type = N'indexed view schema only', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'Indexed View Name', @destination_owner = N'SchemaName', @status = 16 GO5. Function :
USE PublisherDB GO exec sp_addarticle @publication = N'PublicationName', @article = N'FunctionName', @source_owner = N'SchemaName', @source_object = N'FunctionName', @type = N'func schema only', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'FunctionName', @destination_owner = N'SchemaName', @status = 16 GOPlease note these queries run against publisher server and database. I will add one more post next to add articles in existing publications without initialize whole subscription. So stay tuned for more!
Comments
Post a Comment