Thursday, May 15, 2014

To Lists all the Databases by SQL

How can I view the List of databases that available in a SQL server instance via query?

There are many ways to see all the databases, here i've listed 3 types

1. master..sysdatabases - system table added as view for backward compatabilty

2. sys.databases - view available in master & user databases. minimum permission is enough to view the output

3. sp_databases - Inbuilt stored Procedure, to fetch DB lists.
Requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY DEFINITION permission, and must have access permission to the database. Cannot be denied VIEW ANY DEFINITION permission. everyone can not access this SP, if you dont have permission, then you will see blank rows.
USE master;

SELECT Name FROM master..sysdatabases  

SELECT * from sys.databases

EXEC sp_databases; 

Sample Output: 

No comments:

Post a Comment