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.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.