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.

[sourcecode language=”sql”]

SELECT

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 ‘

FROM

information_schema.table_constraints AS Constraints

JOIN

information_schema.key_column_usage AS KeyColumn

ON

Constraints.table_name=KeyColumn.table_name

AND

Constraints.constraint_name=KeyColumn.constraint_name

WHERE

Constraints.constraint_type=’Primary Key’

GO

[/sourcecode]

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

http://Extreme-Advice.com

http://www.sqlhub.com

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

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

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

Comments are closed.