Script to get articles detail - SQL Server Replication by serverku

Before a some months, I shared a one script which is relevant to replication as below, Please have a read here. Sometime we need to have information of articles for reports or to check the article exist or not, in which publication it have and for which subscribers. So this kind of details we can achieve with below script which is run against in publication database,

USE PublisherDB
GO

SELECT sub.srvname, -- Subscriber Server name 
       sub.dest_db, -- Subscriber Database Name
       pub.name, -- Publication name
       art.name, -- Article name
       art.dest_table, -- Published Object name
       art.dest_owner -- Published Schema name
FROM   sysextendedarticlesview art 
       INNER JOIN syspublications pub 
               ON ( art.pubid = pub.pubid ) 
       INNER JOIN syssubscriptions sub 
               ON ( sub.artid = art.artid ) 

GO

Hope this script may be very useful to you and you enjoyed a lot. Have a great day and stay tuned for more!

Copy nonclustered indexes to subscriber while generating snapshot - SQL Server Replication by serverku

Few days back, I was working to add new table in a publication in transaction replication. After adding an article in a publication and after reinitialized, I see non-clustered index was not copied at subscriber database. I tried reinitialized one more time and had a same issue. Let me generate scenario and look forward to the solution after that,

USE [PublisherDB]
GO

CREATE TABLE [dbo].[EmpList]
(
 [EmpId] [int] NOT NULL PRIMARY KEY,
 [EmpName] [varchar](100) NULL,
 [EmpCity] varchar(100) NULL,
 [EmpAddress] [varchar](200) NULL
)

CREATE NONCLUSTERED INDEX IX_EmpList_Name ON [dbo].[EmpList] ([EmpName]) 

I added above created a table in publication and reinitialized subscription with new snapshot and check indexes of that table at both publisher and subscriber side,

EXEC sp_helpindex [EmpList]

Non-clustered is not copied to subscriber after new snapshot applied. After some workaround I found a solution. Make true for the option of copy nonclustered indexes from article property,
Go to Publication property –>  Articles tab –> Article properties –> Set Properties of Highlighted Table Article or Set Properties of All Table Articles and apply changes as following,

After applying changes above and new snapshot generated after that, it copied non clustered indexes to the subscriber database. There are some other options you can find there,

All existing non-clustered indexes copied after new snapshot applied, but I created one more non-clustered index on that table and it's not copied subscriber database without generating a new snapshot. That new index copied after only new snapshot applied of that table. So I am leaving one discussion open here, why that newly created non-clustered index not copied without generating a new snapshot? Any alternative way or recommends solution?