How to check if the column is replicated? (sys.columns..is_replicated is misleading)

  • I have a column-level transactional replication. Usually, I use the following query to check if a column is replicated or not.

    select is_replicated

    from sys.columns

    where name ='Column_Name'

    This works most of the time.

    However, after increasing the width of a published column, the is_replicated value is set to 0. However, in SSMS the column still shows as enabled for replication.

    I ran an update on the column and found that data is replicated to the subscriber. So replication is working fine. Only the is_replicated flag is set to zero.

    In this case, what’s the true test to check if a column is replicated or not? (I need a T-SQL code to verify this to be used in one of my stored procedures.)

    Note: People who want to dive deep into this, I found a system view, syncobj_0xNNNNNNNNN, created when I altered the column. I did some research and found that the view is used to replication thenceforth. However, I am not bothered about this view much, as long as replication works fine.

  • I was just wondering the same and ended up using:

    SELECT source_owner,article
    FROM distribution.dbo.MSarticles
    WHERE publisher_db = @Database

    Where the article is the table name and source_owner is the schema of the article (table) in the publisher end.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply