SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems