SQL Server 2022

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

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

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