Listing All MS SQL Server Databases Using T-SQL
In this short tutorial, I would like to show t-sql examples displaying list of databases in a MS SQL Server instance.
sys.sysdatabases
The of the simplest method in MS SQL Server to list all databases in the related SQL Server instance is running a select query on the sys.sysdatabases system catalog which holds information on existing databases.
SELECT * FROM sys.sysdatabases
master..sysdatabases
An other similar method of listing all the databases in the current SQL Server instance is executing a select query on the sysdatabases system view in master database.
SELECT * FROM master..sysdatabases
sp_databases
You can also call the sp_databases stored procedure in order to get database list in the SQL Server.
This system stored procedure actually filters data and displays returned rows from sys.master_files catalog view.
One important information about sp_databases is, sp_databases returns only databases that the executing user has database access.
So as the process executing user if you do not have access priviliedges to a sql database, that database will not be returned in the sp_databases list.
EXEC sp_databases
sys.master_files
You can run the below t-sql query again querying the sys.master_files catalog view but this time returning all the databases in the Microsoft SQL Server installation.
SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' AND file_id = 1
sp_helpdb
An other t-sql method which you can list all databases that you have access is calling the sp_helpdb stored procedure.
sp_helpdb system stored procedure actually queries the dbo.sysdatabases in master database.
Then in the proc, if the user does not have access to a database this database is removed from the list and the procedure throws or raises the message number 15622 to the user.
EXEC sp_helpdb -- without setting the @dbname parameter
-- Returned output message may be like below :
-- No permission to access database 'SampleUnAuthorizedDatabase'.
The returned message informing the user about the databases that he or she does not have access is the difference between sp_helpdb and sp_databases stored procedures.
sp_MSForEachDB
Of course you can run a customized query by calling the un-documented stored procedure sp_MSForEachDB in order to list all sql databases which you have access like a t-sql statement shown below:
EXEC sp_MSForEachDB 'SELECT ''?'''
More Tutorials on T-SQL sp_MSForEachTable Examples
sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications