By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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');
9 rows affected
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);
5 rows affected
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);
7 rows affected
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);
20 rows affected
--SET SHOWPLAN_XML ON;
-- 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;
-- PLEASE TAKE NOTE THAT...
-- there is a server without an instance and database
-- there is an instances without a database
| ServerName | InstanceName | DatabaseName |
|---|---|---|
| FirstServer | GENERAL | master |
| FirstServer | GENERAL | model |
| FirstServer | GENERAL | msdb |
| FirstServer | GENERAL | UserDB1 |
| FirstServer | TAXES | master |
| FirstServer | TAXES | model |
| FirstServer | TAXES | msdb |
| FirstServer | TAXES | TaxesDB |
| SecondServer | GENERAL | null |
| SecondServer | SOCIAL | master |
| SecondServer | SOCIAL | model |
| SecondServer | SOCIAL | msdb |
| SecondServer | SOCIAL | HealthCareDB |
| ThirdServer | GENERAL | master |
| ThirdServer | GENERAL | model |
| ThirdServer | GENERAL | msdb |
| ThirdServer | GENERAL | GeneralUserDB |
| ThirdServer | FBI | master |
| ThirdServer | FBI | model |
| ThirdServer | FBI | msdb |
| ThirdServer | FBI | CriminalDB |
| FourthServer | null | null |
| FifthServer | COMINGSOON | null |
-- 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;
-- PLEASE TAKE NOTE THAT ....
-- the server without instance is no longer listed
-- the instance without database is no longer listed
| ServerName | StatusName | InstanceName | StatusName | DatabaseName | StatusName |
|---|---|---|---|---|---|
| FirstServer | Productive | GENERAL | Productive | master | Productive |
| FirstServer | Productive | GENERAL | Productive | model | Productive |
| FirstServer | Productive | GENERAL | Productive | msdb | Productive |
| FirstServer | Productive | GENERAL | Productive | UserDB1 | Productive |
| FirstServer | Productive | TAXES | Productive | master | Productive |
| FirstServer | Productive | TAXES | Productive | model | Productive |
| FirstServer | Productive | TAXES | Productive | msdb | Productive |
| FirstServer | Productive | TAXES | Productive | TaxesDB | Productive |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | master | Prod ACC |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | model | Prod ACC |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | msdb | Prod ACC |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | HealthCareDB | Prod ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | master | Test ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | model | Test ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | msdb | Test ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | GeneralUserDB | Test ACC |
| ThirdServer | Test ACC | FBI | Reserved | master | Reserved |
| ThirdServer | Test ACC | FBI | Reserved | model | Reserved |
| ThirdServer | Test ACC | FBI | Reserved | msdb | Reserved |
| ThirdServer | Test ACC | FBI | Reserved | CriminalDB | Reserved |
-- 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;
| ServerName | StatusName | InstanceName | StatusName | DatabaseName | StatusName |
|---|---|---|---|---|---|
| FirstServer | Productive | GENERAL | Productive | master | Productive |
| FirstServer | Productive | GENERAL | Productive | model | Productive |
| FirstServer | Productive | GENERAL | Productive | msdb | Productive |
| FirstServer | Productive | GENERAL | Productive | UserDB1 | Productive |
| FirstServer | Productive | TAXES | Productive | master | Productive |
| FirstServer | Productive | TAXES | Productive | model | Productive |
| FirstServer | Productive | TAXES | Productive | msdb | Productive |
| FirstServer | Productive | TAXES | Productive | TaxesDB | Productive |
| SecondServer | Prod ACC | GENERAL | Decommisioned | null | null |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | master | Prod ACC |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | model | Prod ACC |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | msdb | Prod ACC |
| SecondServer | Prod ACC | SOCIAL | Prod ACC | HealthCareDB | Prod ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | master | Test ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | model | Test ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | msdb | Test ACC |
| ThirdServer | Test ACC | GENERAL | Test ACC | GeneralUserDB | Test ACC |
| ThirdServer | Test ACC | FBI | Reserved | master | Reserved |
| ThirdServer | Test ACC | FBI | Reserved | model | Reserved |
| ThirdServer | Test ACC | FBI | Reserved | msdb | Reserved |
| ThirdServer | Test ACC | FBI | Reserved | CriminalDB | Reserved |
| FourthServer | Reserved | null | null | null | null |
| FifthServer | Reserved | COMINGSOON | Reserved | null | null |