sql - TSQL / SQL-SERVER:如何在具有主键的快照复制中查找所有表
问题描述
我的任务是在具有事务复制主键的单个快照复制中移动所有表。我们获得了供应商更新,他们可能已将键添加到快照复制中的表中。
我尝试将其分解为 2 个步骤,在快照复制中查找所有表,然后检查这些表是否具有主键。
我试图拼凑几个不同的代码示例,但我可能需要重新开始,这就是我到目前为止所得到的。
--=============================================================================================
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: docs.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;
解决方案
这就是我最终的结果。我拼凑并调整了我找到的各种代码片段。
一些来自这里:https ://dataedo.com/kb/query/sql-server/list-tables-with-their-primary-keys
--====================================================================================================================================================
-- 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];
推荐阅读
- docker - 如何使用 ECR url 标记 docker compose 图像?
- go - 有什么好的方法来定义 Monoid 接口吗?
- hotkeys - 锤子:如何在某些应用程序中禁用热键?
- sql-server - 如果从快照恢复数据库,SQL Server 会生成一些日志供我们跟踪吗?
- google-apps-script - GAS 将表情符号插入表格
- javascript - Nodejs API 部署到 Heroku
- c# - 使用扩展包时如何修改SSMS(18)中的状态栏和标题背景颜色?
- c - 如何覆盖c中变量的值
- forms - tinymce 是否可以通过菜单选择将表单字段插入其中或仅通过编辑代码?
- python - 关于json合并到python的问题