How to get all Databases name in Sql Server
We can write a query like below to get the list of all databases on an instance of an Sql Server. This list includes both the system databases as well as the user created databases.
SELECT name FROM SYS.DATABASES
RESULT:
How to get only the User Created Databases name in Sql Server
If you want to get only the User Created databases name from an instance of an Sql Server, then we can write a query like below. Here, I am filtering the know System Databases from the result. As far as I know Sql Server doesn’t provide any mechanism with which we can identify a database as a system database or user created database.
SELECT name AS 'User Created Databases'
FROM SYS.DATABASES
WHERE name NOT IN
('master', 'model', 'msdb', 'tempdb', 'Resource',
'distribution' , 'reportserver', 'reportservertempdb')
RESULT:
How to get only the System Databases name in Sql Server
If you want to get only the system databases name from an instance of an Sql Server, then we can write a query like below.
SELECT name
FROM SYS.DATABASES
WHERE name IN ('master', 'model', 'msdb', 'tempdb', 'resource',
'distribution' , 'reportserver', 'reportservertempdb')
RESULT:
The number of System Databases returned varies from one instance of Sql Server to another instance of the Sql Server. For example if you have installed the Sql Server reporting services then only the instance of Sql Server will have the reportserver and reportservertempdb databases.