TSQL/SQL-SERVER:如何在快照复制中查找所有具有主键的表

Question

我的任务是移动具有事务复制主键的单个快照复制中的所有表。我们得到了供应商的更新，他们可能已经向快照复制中的表添加了键。

我尝试将其分解为两个步骤，在快照复制中查找所有表，然后检查这些表是否有主键。

我已经试着拼凑出一些不同的代码示例，但我可能需要重新开始，以下是我迄今为止得到的内容。

--=============================================================================================
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;

Answer 1

这就是我最终要做的。我拼凑并调整了我发现的各种代码片段。

这里的一些：https://dataedo.com/kb/query/sql-server/list-tables-with-their-primary-keys

此处的其他代码：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];