Script to add articles in publication - SQL Server transactional replication by serverku

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 :

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
GO

2. 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
GO

3. 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
GO

4. 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
GO

5. 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
GO

Please 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!