我最近正在查询我们的内部数据库库存工具以获取服务器、实例和数据库的列表,并向每个服务器、实例和数据库添加相应的状态。
关系图
Server ˂-- 1 : n --˃ Instance ˂-- 1 : n --˃ Database
˄ ˄ ˄
| | |
| 1 : 1 |
| | |
| ˅ |
+-- 1 : 1 --˃ Status ˂-- 1 : 1 --+
读作:
...一个服务器可以有多个实例
...一个实例可以有多个数据库
...一个服务器、一个实例和一个数据库可以有一个状态
设置
状态表
CREATE TABLE [Status]
(
StatusID int,
StatusName char(20),
);
状态数据
INSERT INTO [Status] (StatusID, StatusName)
VALUES
(1,'Productive'),
(2,'Prod ACC'),
(3,'Prod APP'),
(4,'Test'),
(5,'Test ACC'),
(6,'Test APP'),
(7,'OFFLINE'),
(8,'Reserved'),
(9,'Decommisioned');
服务器表
CREATE TABLE [Server]
(
ServerID int,
ServerName char(20),
ServerStatusID int
);
服务器数据
INSERT INTO [Server] (ServerID, ServerName, ServerStatusID)
VALUES
(1,'FirstServer',1),
(2,'SecondServer',2),
(3,'ThirdServer',5),
(4,'FourthServer',8),
(5,'FifthServer',8);
实例表
CREATE TABLE [Instance]
(
InstanceID int,
ServerID int,
InstanceName char(30),
InstanceStatusID int
);
实例数据
INSERT INTO [Instance]
(InstanceID, ServerID, InstanceName, InstanceStatusID)
VALUES
(1,1,'GENERAL',1),
(2,1,'TAXES',1),
(3,2,'GENERAL',9),
(4,2,'SOCIAL',2),
(5,3,'GENERAL',5),
(6,3,'FBI',8),
(7,5,'COMINGSOON',8);
数据库表
CREATE TABLE [Database]
(
DatabaseID int,
InstanceID int,
DatabaseName char(30),
DatabaseStatusID int
);
数据库数据
INSERT INTO [Database]
(DatabaseID, InstanceID, DatabaseName, DatabaseStatusID)
VALUES
(1,1,'master',1),
(2,1,'model',1),
(3,1,'msdb',1),
(4,1,'UserDB1',1),
(5,2,'master',1),
(6,2,'model',1),
(7,2,'msdb',1),
(8,2,'TaxesDB',1),
(9,4,'master',2),
(10,4,'model',2),
(11,4,'msdb',2),
(12,4,'HealthCareDB',2),
(13,5,'master',5),
(14,5,'model',5),
(15,5,'msdb',5),
(16,5,'GeneralUserDB',5),
(17,6,'master',8),
(18,6,'model',8),
(19,6,'msdb',8),
(20,6,'CriminalDB',8);
不涉及状态表的 SELECT 语句
初始 SELECT 语句仅涉及连接三个表:服务器、实例、数据库,如下所示:
-- Simple SELECT to get all information on Servers, Instances and Databases
-- The status of the server, instance or database is not returned
SELECT
ServerName,
InstanceName,
DatabaseName
FROM [Server] as srv
LEFT JOIN [Instance] as ins
ON srv.ServerID = ins.ServerID
LEFT JOIN [Database] as dbs
ON ins.InstanceID = dbs.InstanceID;
1. 声明的结果
请注意...
- 有一台没有实例和数据库的服务器
- 有一个实例没有数据库
服务器名称 | 实例名称 | 数据库名称 |
---|---|---|
第一服务器 | 一般的 | 掌握 |
第一服务器 | 一般的 | 模型 |
第一服务器 | 一般的 | 数据库数据库 |
第一服务器 | 一般的 | 用户数据库1 |
第一服务器 | 税收 | 掌握 |
第一服务器 | 税收 | 模型 |
第一服务器 | 税收 | 数据库数据库 |
第一服务器 | 税收 | 税收数据库 |
第二服务器 | 一般的 | 无效的 |
第二服务器 | 社会的 | 掌握 |
第二服务器 | 社会的 | 模型 |
第二服务器 | 社会的 | 数据库数据库 |
第二服务器 | 社会的 | 医疗保健数据库 |
第三服务器 | 一般的 | 掌握 |
第三服务器 | 一般的 | 模型 |
第三服务器 | 一般的 | 数据库数据库 |
第三服务器 | 一般的 | 通用用户数据库 |
第三服务器 | 联邦调查局 | 掌握 |
第三服务器 | 联邦调查局 | 模型 |
第三服务器 | 联邦调查局 | 数据库数据库 |
第三服务器 | 联邦调查局 | 刑事数据库 |
第四服务器 | 无效的 | 无效的 |
第五服务器 | 即将推出 | 无效的 |
涉及 Status 表的 SELECT 语句
在下一个语句中,我决定将状态添加到每个元素(服务器、实例、数据库),并JOIN
使用表编辑每个表,Status
如下所示:
-- Advanced SELECT to get all information on Servers, Instances and Databases
-- including their status
SELECT
ServerName,
srvst.StatusName,
InstanceName,
insst.StatusName,
DatabaseName,
dbsst.StatusName
FROM [Server] as srv
JOIN [Status] as srvst
ON srv.ServerStatusID = srvst.StatusID
LEFT JOIN [Instance] as ins
ON srv.ServerID = ins.ServerID
JOIN [Status] as insst
ON ins.InstanceStatusID = insst.StatusID
LEFT JOIN [Database] as dbs
ON ins.InstanceID = dbs.InstanceID
JOIN [Status] as dbsst
ON dbs.DatabaseStatusID = dbsst.StatusID
;
2. 声明结果
令我惊讶的是,没有实例和数据库的服务器以及有实例但没有数据库的服务器不再列出:
服务器名称 | 状态名称 | 实例名称 | 状态名称 | 数据库名称 | 状态名称 |
---|---|---|---|---|---|
第一服务器 | 富有成效 | 一般的 | 富有成效 | 掌握 | 富有成效 |
第一服务器 | 富有成效 | 一般的 | 富有成效 | 模型 | 富有成效 |
第一服务器 | 富有成效 | 一般的 | 富有成效 | 数据库数据库 | 富有成效 |
第一服务器 | 富有成效 | 一般的 | 富有成效 | 用户数据库1 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 掌握 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 模型 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 数据库数据库 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 税收数据库 | 富有成效 |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 掌握 | 产品ACC |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 模型 | 产品ACC |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 数据库数据库 | 产品ACC |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 医疗保健数据库 | 产品ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 掌握 | 测试ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 模型 | 测试ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 数据库数据库 | 测试ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 通用用户数据库 | 测试ACC |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 掌握 | 预订的 |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 模型 | 预订的 |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 数据库数据库 | 预订的 |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 刑事数据库 | 预订的 |
调查结果/解决方案
通过反复试验的方法检查了各种选项后,我发现必须将表JOIN
上的Status
更改为 a ,LEFT JOIN
以允许该语句显示没有实例或数据库的服务器,并显示没有数据库的实例:
-- Advanced SELECT to get all information on Servers, Instances and Databases
-- including their status
SELECT
ServerName,
srvst.StatusName,
InstanceName,
insst.StatusName,
DatabaseName,
dbsst.StatusName
FROM [Server] as srv
LEFT JOIN [Status] as srvst
ON srv.ServerStatusID = srvst.StatusID
LEFT JOIN [Instance] as ins
ON srv.ServerID = ins.ServerID
LEFT JOIN [Status] as insst
ON ins.InstanceStatusID = insst.StatusID
LEFT JOIN [Database] as dbs
ON ins.InstanceID = dbs.InstanceID
LEFT JOIN [Status] as dbsst
ON dbs.DatabaseStatusID = dbsst.StatusID;
3. 声明的结果
服务器名称 | 状态名称 | 实例名称 | 状态名称 | 数据库名称 | 状态名称 |
---|---|---|---|---|---|
第一服务器 | 富有成效 | 一般的 | 富有成效 | 掌握 | 富有成效 |
第一服务器 | 富有成效 | 一般的 | 富有成效 | 模型 | 富有成效 |
第一服务器 | 富有成效 | 一般的 | 富有成效 | 数据库数据库 | 富有成效 |
第一服务器 | 富有成效 | 一般的 | 富有成效 | 用户数据库1 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 掌握 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 模型 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 数据库数据库 | 富有成效 |
第一服务器 | 富有成效 | 税收 | 富有成效 | 税收数据库 | 富有成效 |
第二服务器 | 产品ACC | 一般的 | 退役 | 无效的 | 无效的 |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 掌握 | 产品ACC |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 模型 | 产品ACC |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 数据库数据库 | 产品ACC |
第二服务器 | 产品ACC | 社会的 | 产品ACC | 医疗保健数据库 | 产品ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 掌握 | 测试ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 模型 | 测试ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 数据库数据库 | 测试ACC |
第三服务器 | 测试ACC | 一般的 | 测试ACC | 通用用户数据库 | 测试ACC |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 掌握 | 预订的 |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 模型 | 预订的 |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 数据库数据库 | 预订的 |
第三服务器 | 测试ACC | 联邦调查局 | 预订的 | 刑事数据库 | 预订的 |
第四服务器 | 预订的 | 无效的 | 无效的 | 无效的 | 无效的 |
第五服务器 | 预订的 | 即将推出 | 预订的 | 无效的 | 无效的 |
参考资料
这里有一个db<>fiddle的链接来重现我的发现。
问题
为什么 SQL Server 需要在表LEFT JOIN
中Status
显示不存在的子项以及查询来显示这些项?