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.


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

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.

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.