Advertisement
Advertisement


Get list of databases from SQL Server


Question

How can I get the list of available databases on a SQL Server instance? I'm planning to make a list of them in a combo box in VB.NET.

2014/07/08
1
391
7/8/2014 3:17:48 PM

Accepted Answer

Execute:

SELECT name FROM master.sys.databases

This the preferred approach now, rather than dbo.sysdatabases, which has been deprecated for some time.


Execute this query:

SELECT name FROM master.dbo.sysdatabases

or if you prefer

EXEC sp_databases
2018/05/28
623
5/28/2018 4:20:06 PM

in light of the ambiguity as to the number of non-user databases, you should probably add:

WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

and add the names of the reporting services databases

2011/05/26

To exclude system databases:

SELECT [name]
FROM master.dbo.sysdatabases
WHERE dbid > 6

Edited : 2:36 PM 2/5/2013

Updated with accurate database_id, It should be greater than 4, to skip listing system databases which are having database id between 1 and 4.

SELECT * 
FROM sys.databases d
WHERE d.database_id > 4
2013/02/05

SELECT [name] 
FROM master.dbo.sysdatabases 
WHERE dbid > 4 

Works on our SQL Server 2008

2016/01/15

Since you are using .NET you can use the SQL Server Management Objects

Dim server As New Microsoft.SqlServer.Management.Smo.Server("localhost")
For Each db As Database In server.Databases
    Console.WriteLine(db.Name)
Next
2011/08/07

Don't Get confused, Use the below simple query to get all the databases,

select * from sys.databases

If u need only the User defined databases;

select * from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'); 

Some of the System database names are (resource,distribution,reportservice,reportservicetempdb) just insert it into the query. If u have the above db's in your machine as default.

2014/07/18

Source: https://stackoverflow.com/questions/147659
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]