In this article, we will learn how to get a list of user-created databases, system databases and all the databases from an instance of SQL Server.
To get a list of all the databases on an instance of SQL Server, write a query as given below. This query returns a list that includes both the system databases as well as the user-created databases.
SELECT * FROM master.sys.databases;
SELECT * FROM SYS.DATABASES;
SELECT * FROM master.dbo.sysdatabases;
And using these following stored procedures, we can also get a list of all the databases on an instance of SQL Server. These stored procedures return a list that includes both the system databases as well as the user-created databases.
EXEC sp_helpdb
EXEC sp_databases
If you want to get a list of system databases name on an instance of SQL server, a query is as follows:
SELECT name AS 'System Database'
FROM SYS.DATABASES
WHERE name IN ('master', 'model', 'msdb', 'tempdb', 'resource',
'distribution' , 'reportserver', 'reportservertempdb');
The Fifth Hidden System Database in SQL Server
If you want to get a list of user-created databases name on an instance of SQL Server, a query is as follows:
SELECT name AS 'User Created Database'
FROM SYS.DATABASES
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'resource',
'distribution' , 'reportserver', 'reportservertempdb')
I hope this article will help you to understand how to get a list of user-created databases, system databases and all the databases from an instance of SQL Server.
Share your valuable feedback, please post your comment at the bottom of this article. Thank you!
Comments