T-Sql Script for Automating Replication Generation for all Stored Procedures in a SQL Server Database

This involves following steps:

• Creating stored procedure named Create_Replication_SP using the code provided in this post. Please find the T-sql script below that can be used to create,configure and run replication for all stored procedures in a database automatically. 
• Once stored procedure is in place all you have to do is provide the name of the database as the parameter as shown in example below:

Use NameOfDatabasewhereSpIsCreated
GO
EXEC [Create_Replication_SP] @Database_Name='NameOfDatabaseHere'

• This will generate and Print a script in return in messages tab in SSMS. 
• Copy the generated script and paste it in new query window and execute it and wait for its completion, progress gets tracked in Messages tab in Output window. 

What script does:

1. This creates Publication with nomenclature DatabaseName_SPor if you want a custom name you can pass the parameter @Publication_Name along with @DatabaseName (should be under 20 characters). 
2. Creates a snapshot agent 
3. Gets all the stored procedures in the database except system databases and add's them as articles to publication. 
4. Starts the snapshot agent and created the snapshot. 

Once snapshot is created (can be checked from replication monitor)run the followng t-sql to add subscribers(don't forget to change parameters name accordingly):

Use NameOfDatabaseHere
exec sp_addsubscription @publication = N'NameOfDatabaseHere_sp', 
@subscriber = N'NameOfSubscriberInstanceHere', 
@destination_db = N'NameOfDatabaseHere', 
@subscription_type = N'Push', 
@sync_type = N'automatic', 
@article = N'all', 
@update_mode = N'read only', 
@subscriber_type = 0

How this is helpful? 
After every stage refresh we had to do this manually now we can use this tsql along with powershell to do this complete task automatically and add it to our stage refresh automation job.
T-sql to create Create_Replication_SP procedure is given below:

/****** 
Object:  StoredProcedure [dbo].[Create_Replication_SP]    
Script Author: SHIVAM KUMAR 
******/
USE [NameOfDatabasewhereOurSpIsCreated]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Create_Replication_SP] @Database_Name sysname , @Publication_Name varchar(20) = Null
AS
BEGIN
 --  Declare and Set values to Local variables
 Declare @DB_Name sysname
 Declare @Pub_Name varchar(20)

 SET @DB_Name = @Database_Name
 IF @Publication_Name IS NULL
  SET @Pub_Name = @DB_Name+'_SP'
 ELSE
  SET @Pub_Name = @Publication_Name

 PRINT 'USE ' + @DB_Name
 PRINT 'GO'
 -- Add Transcational Publication.
 PRINT 'PRINT ''Add Transcational Publication'''
 PRINT 'exec sp_addpublication @publication = N'''+@Pub_Name+''', 
@description = N''Transactional publication of database' + @DB_Name + ' from Publisher ' +  @@SERVERNAME +''', 
@sync_method = N''concurrent'', 
@retention = 0, 
@allow_push = N''true'', 
@allow_pull = N''true'', 
@allow_anonymous = N''true'', 
@enabled_for_internet = N''false'', 
@snapshot_in_defaultfolder = N''true'', 
@compress_snapshot = N''false'', 
@ftp_port = 21, 
@ftp_login = N''anonymous'', 
@allow_subscription_copy = N''false'', 
@add_to_active_directory = N''false'', 
@repl_freq = N''continuous'', 
@status = N''active'', 
@independent_agent = N''true'', 
@immediate_sync = N''true'', 
@allow_sync_tran = N''false'', 
@autogen_sync_procs = N''false'', 
@allow_queued_tran = N''false'', 
@allow_dts = N''false'', 
@replicate_ddl = 1, 
@allow_initialize_from_backup = N''false'', 
@enabled_for_p2p = N''false'', 
@enabled_for_het_sub = N''false'''
 PRINT 'GO'
 --Add SnapShot Agenet.
 PRINT 'PRINT ''Add SnapShot Agents'''
 PRINT 'exec sp_addpublication_snapshot @publication = N'''+@Pub_Name+''', 
@frequency_type = 1, 
@frequency_interval = 0, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 0, 
@frequency_subday_interval = 0, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, 
@active_start_date = 0, 
@active_end_date = 0, 
@job_login = null, 
@job_password = null, 
@publisher_security_mode = 1'
PRINT 'GO'
-- Add Permissions to Replication.
PRINT 'EXEC sp_grant_publication_access @publication = N'''+@Pub_Name+''', @login = N''AWS\YourSqlServerServiceAccountHere'''
PRINT 'GO'
PRINT 'EXEC sp_grant_publication_access @publication = N'''+@Pub_Name+''', @login = N''AWS\AnyOtherAccoutYouWantToGiveAccessTo'''
PRINT 'GO'
PRINT 'EXEC sp_grant_publication_access @publication = N'''+@Pub_Name+''', @login = N''NT SERVICE\Winmgmt'''
PRINT 'GO'
PRINT 'EXEC sp_grant_publication_access @publication = N'''+@Pub_Name+''', @login = N''NT SERVICE\SQLWriter'''
PRINT 'GO'
PRINT 'EXEC sp_grant_publication_access @publication = N'''+@Pub_Name+''', @login = N''NT SERVICE\SQLSERVERAGENT'''
PRINT 'GO'
PRINT 'EXEC sp_grant_publication_access @publication = N'''+@Pub_Name+''', @login = N''NT Service\MSSQLSERVER'''
PRINT 'GO'
PRINT 'EXEC sp_grant_publication_access @publication = N'''+@Pub_Name+''', @login = N''distributor_admin'''
PRINT 'GO'
PRINT ''
 -- Adding Articles
 Declare @addar nvarchar(max)
 Declare @sprepl varchar(250)
        DECLARE sprepl_cursor CURSOR FOR  
        --Get all stored procedures except system procedures
        --Change DatabaseName here from which you want all stored procedures
 select Name from NameOfDatabaseHere.sys.procedures where is_ms_shipped = 0 
 OPEN sprepl_cursor
 FETCH NEXT from sprepl_cursor into @sprepl
 WHILE @@FETCH_STATUS = 0
 BEGIN 
--- Add Articles to Publication.
PRINT 'PRINT ''Working on Stored Procedure ' + @sprepl + ''''
PRINT 'GO'
PRINT 'USE ' + @DB_Name
select @addar = 'Exec sp_addarticle @publication = N'''+@Pub_Name+''', 
@article = N'''+@sprepl+''', 
@source_owner = N''dbo'', 
@source_object = N'''+@sprepl+''', 
@type = N''proc schema only'', 
@description = null, 
@creation_script = null, 
@pre_creation_cmd = N''drop'', 
@schema_option = 0x0000000008000001, 
@destination_table = N'''+@sprepl+''', 
@destination_owner = N''dbo'''
  Print @addar
  PRINT 'GO'
  Print ''
  FETCH NEXT from sprepl_cursor into @sprepl
  END
  CLOSE sprepl_cursor   
 DEALLOCATE sprepl_cursor
-- Start the Snapshot Agent job.
PRINT 'EXEC sp_startpublication_snapshot @publication = N'''+@Pub_Name+''''
PRINT'GO'
END
GO