CODEWITHSUNDEEP
sql-server
sef
sef

Reputation: 5305

Get list of databases from SQL Server

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.

Upvotes: 485

Views: 1156328

Answers (15)

GilM
GilM

Reputation: 3771

To exclude system databases:

SELECT * 
FROM sys.databases d
WHERE d.database_id > 4

System databases have database id between 1 and 4.

Upvotes: 63

Akshay Vilas Patil
Akshay Vilas Patil

Reputation: 144

To list all available databases in MS-SQL Server using T-SQL, you can execute the following query:

SELECT name, database_id, create_date FROM sys.databases;
GO

If you want to exclude system databases just add below condition in query:

SELECT name, database_id, create_date FROM sys.databases WHERE database_id > 4;
GO

Source: View list of databases on SQL Server

Upvotes: 2

Balaji
Balaji

Reputation: 1515

Use the query below to get all the databases:

select * from sys.databases

If you 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 you have the above db's in your machine as default.

Upvotes: 35

gobi
gobi

Reputation: 515

If you are looking for a command to list databases in MYSQL, then just use the below command. After login to sql server,

show databases;

Upvotes: -1

Tarık Özgün Güner
Tarık Özgün Güner

Reputation: 1081

If you want to omit system databases and ReportServer tables (if installed)

select DATABASE_NAME = db_name(s_mf.database_id)
from sys.master_files s_mf
where
    s_mf.state = 0 -- ONLINE
    and has_dbaccess(db_name(s_mf.database_id)) = 1
    and db_name(s_mf.database_id) NOT IN ('master', 'tempdb', 'model', 'msdb')
    and db_name(s_mf.database_id) not like 'ReportServer%'
group by s_mf.database_id
order by 1;

This works on SQL Server 2008/2012/2014. Most of query comes from "sp_databases" system stored procedure. I only removed unneeded column and added where conditions.

Upvotes: 6

Ben Hoffstein
Ben Hoffstein

Reputation: 103395

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

Upvotes: 770

Luca
Luca

Reputation: 1

To exclude system databases :

SELECT name FROM master.dbo.sysdatabases where sid <>0x01

Upvotes: -4

thedanotto
thedanotto

Reputation: 7327

perhaps I'm a dodo!

show databases; worked for me.

Upvotes: -1

Frank
Frank

Reputation: 309

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

Works on our SQL Server 2008

Upvotes: 30

watch_amajigger
watch_amajigger

Reputation: 21

Not sure if this will omit the Report server databases since I am not running one, but from what I have seen, I can omit system user owned databases with this SQL:

    SELECT  db.[name] as dbname 
    FROM [master].[sys].[databases] db
    LEFT OUTER JOIN  [master].[sys].[sysusers] su on su.sid = db.owner_sid
    WHERE su.sid is null
    order by db.[name]

Upvotes: 2

Rob Prouse
Rob Prouse

Reputation: 22657

I use the following SQL Server Management Objects code to get a list of databases that aren't system databases and aren't snapshots.

using Microsoft.SqlServer.Management.Smo;

public static string[] GetDatabaseNames( string serverName )
{
   var server = new Server( serverName );
   return ( from Database database in server.Databases 
            where !database.IsSystemObject && !database.IsDatabaseSnapshot
            select database.Name 
          ).ToArray();
}

Upvotes: 6

ManiG
ManiG

Reputation: 83

SELECT [name] 
FROM master.dbo.sysdatabases 
WHERE dbid > 4 and [name] <> 'ReportServer' and [name] <> 'ReportServerTempDB'

This will work for both condition, Whether reporting is enabled or not

Upvotes: 8

Chris Diver
Chris Diver

Reputation: 19852

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

Upvotes: 23

JerryOL
JerryOL

Reputation: 1449

In SQL Server 7, dbid 1 thru 4 are the system dbs.

Upvotes: 1

GilShalit
GilShalit

Reputation: 6493

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

Upvotes: 97

Related Questions