I've been tasked with moving all tables in a single snapshot replication that have primary keys to the transaction replication. We get vendor updates and they may have added keys to tables that were in the Snapshot replication.
I've tried to break it down into 2 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.
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t2 ON t2.TABLE_NAME = so.name ON so.id = o.object_id
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 @jobId UNIQUEIDENTIFIER;
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;
This is what I ended up going with. I pieced together and tweaked various snippets of code I've found.
Some from here: https://dataedo.com/kb/query/sql-server/list-tables-with-their-primary-keys
Other code from here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50c6890b-8dc1-46c6-aeda-d97149a9692f/list-all-replicated-tables-and-their-destination?forum=sqlreplication
--====================================================================================================================================================
-- 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
CROSS APPLY (
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];