Generate script to add multiple table in replication as article

Generate script to add multiple table in replication as article

I have already explained about “How to add article in replication from GUI” and “How to add article in replication from TSQL”. I am a script buddy and as long as possible I wouldn’t like to go for GUI options.

If there would be two or four objects only, we could create add article script manually but what about creating a script for many tables or objects?

Generally I used to select only one small table while setting up replication and once replication is ready with publisher, distributor and subscription, I add remaining objects (mainly tables) via script. So after setting up replication, I may need to add hundreds of table and creating add article script for all is little hectic. I have created one small script to overcome this.


DISTINCT 'DECLARE @tableName' + Constraints.table_name+' VARCHAR(255)'

+ ' DECLARE @SchemeName' + Constraints.table_name+' VARCHAR(255)'

+ ' SET @tableName' + Constraints.table_name+'='+ '''' + Constraints.table_name + ''''

+ ' SET @SchemeName' + Constraints.table_name+'='+ '''' + Constraints.constraint_schema + ''''

+ ' exec sp_addarticle'

+ ' @publication = N''AdventureWork2012Pub'', '

+ ' @article =@tableName' + Constraints.table_name+','

+ ' @source_owner =@SchemeName' + Constraints.table_name+','

+ ' @source_object =@tableName' + Constraints.table_name+','

+ ' @type = N''logbased'', '

+ ' @description = null, '

+ ' @creation_script = null, '

+ ' @pre_creation_cmd = N''drop'', '

+ ' @schema_option = 0x000000000803509F,'

+ ' @identityrangemanagementoption = N''manual'', '

+ ' @destination_table =@tableName' + Constraints.table_name+','

+ ' @destination_owner =@SchemeName' + Constraints.table_name+','

+ ' @force_invalidate_snapshot=1 '


information_schema.table_constraints AS Constraints


information_schema.key_column_usage AS KeyColumn






Constraints.constraint_type='Primary Key'


This script find all tables which has primary key and create add article script for them. Transactional Replication needs to have primary key on table to make it part of replication.

My publication name was “AdventureWork2012Pub” so I have used it in the script, you can have your own publication name there.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles.

One thought on “Generate script to add multiple table in replication as article

  1. Pingback: Keep an eye on replication error in SQL Server

Comments are closed.