I've been tasked with moving all tables in a single snapshot replication that have primary keys to the transaction replication.

I've tried to break it down into two steps, finding all tables in a snapshot replication, and then checking to see if those tables have a primary key.

I've tried to piece together a few different code samples, but I may need to start over, here's what I've got so far.


SELECT  DB_NAME ()                  PublisherDB
  , sp.name                     AS PublisherName
  , sa.name                     AS TableName
  , UPPER (srv.srvname)         AS SubscriberServerName
FROM    dbo.syspublications   sp
JOIN    dbo.sysarticles       sa ON sp.pubid = sa.pubid
JOIN    dbo.syssubscriptions  s ON sa.artid = s.artid
JOIN    master.dbo.sysservers srv ON s.srvid = srv.srvid;

SELECT          DB_NAME ()                    AS db
              , SCHEMA_NAME (o.schema_id)     AS [Schema]
              , so.name                       AS table_name
              , so.type
              , CASE WHEN TABLE_NAME IN (
                              SELECT    TABLE_NAME
                              FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                              WHERE     CONSTRAINT_TYPE = 'PRIMARY KEY'
                          ) THEN 1 ELSE 0 END AS HasPrimaryKey
--INTO            #t2
FROM            sys.objects o WITH (NOLOCK)
INNER JOIN      sysobjects                                      so WITH (NOLOCK)
--INNER JOIN #t1 ON t1.
WHERE           (
        (so.xtype = 'U') -- user table   xtype: learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-ver15
    OR  (so.xtype = 'V') -- view
    OR  (so.xtype = 'P') -- stored procedure
                AND so.category <> 2
                AND so.name IN (
                        SELECT  DISTINCT OBJECT_NAME (objid) FROM  dbo.sysarticles
ORDER BY        so.name
              , so.type;


DECLARE @jobName sysname;

SELECT  @jobId   = jobs.job_id
      , @jobName = jobs.name
FROM    msdb.dbo.sysjobs       jobs (NOLOCK)
JOIN    msdb.dbo.syscategories categories (NOLOCK) ON jobs.category_id = categories.category_id
WHERE   categories.name = 'REPL-Snapshot'
        AND jobs.name LIKE '%db-name%';

SELECT  @jobId
      , @jobName;

EXEC sp_start_job @job_id = @jobId;

1 Answer 1


This is what I ended up going with.

--  Get tables in Snapshot replication for the selected DB.

IF OBJECT_ID ('tempdb..#t1') IS NOT NULL DROP TABLE #t1;

SELECT      pub.name                                                                AS [Publication]
          , CASE WHEN pub.name LIKE '%Snapshot%' THEN 'SnapShot'
                WHEN pub.name LIKE '%Transaction%' THEN 'Transaction' ELSE NULL END AS ReplicationType
          , art.name                                                                AS [Article]
          , serv.name                                                               AS [Subsriber]
          , sub.dest_db                                                             AS [DestinationDB]
          , obj.object_id
          , CASE WHEN obj.type = 'U' THEN 'Table'
                WHEN obj.type = 'V' THEN 'View'
                WHEN obj.type = 'P' THEN 'SP' ELSE NULL END                         AS ObjectType
INTO        #t1
FROM        dbo.syssubscriptions sub
INNER JOIN  sys.servers          serv ON serv.server_id = sub.srvid
INNER JOIN  dbo.sysarticles      art ON art.artid = sub.artid
INNER JOIN  dbo.syspublications  pub ON pub.pubid = art.pubid
INNER JOIN  sys.objects          obj ON obj.object_id = art.objid
WHERE       CASE WHEN pub.name LIKE '%Snapshot%' THEN 'SnapShot'
                WHEN pub.name LIKE '%Transaction%' THEN 'Transaction' ELSE NULL END = 'Snapshot';

--  Check for primary keys on the above tables
SELECT          SCHEMA_NAME (tab.schema_id)                         AS [schema_name]
              , tab.[name]                                          AS table_name
              , pk.[name]                                           AS pk_name
              , SUBSTRING (column_names, 1, LEN (column_names) - 1) AS [columns]
FROM            sys.tables  tab
LEFT OUTER JOIN sys.indexes pk ON tab.object_id = pk.object_id
                                  AND   pk.is_primary_key = 1
    SELECT      col.[name] + ', '
    FROM        sys.index_columns ic
    INNER JOIN  sys.columns       col ON ic.object_id = col.object_id
                                         AND  ic.column_id = col.column_id
    WHERE       ic.object_id = tab.object_id
                AND ic.index_id = pk.index_id
    ORDER BY    col.column_id
    FOR XML PATH ('')
)                           D(column_names)
WHERE           pk.object_id IN (
                    SELECT  object_id FROM  #t1
ORDER BY        SCHEMA_NAME (tab.schema_id)
              , tab.[name];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.