;

How to Get a List of all Databases from SQL Server Instance


Tutorialsrack 02/10/2019 SQL Server

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.

How To Get a List of All the Databases Name in 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.

Example - To get a list of all the databases on an instance of SQL Server
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.

Example -  To get a list of all the databases using the Stored Procedure
EXEC sp_helpdb

EXEC sp_databases

How to get only the list of System Databases name in SQL Server

If you want to get a list of system databases name on an instance of SQL server, a query is as follows:

Example - To get a list of system databases name on an instance of SQL server
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

How to get only the list of User-Created Databases name 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:

Example - To get a list of user-created databases name on an instance of SQL Server
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!


Related Posts



Comments

Recent Posts
Tags