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.
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
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
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
SELECT [name]
FROM master.dbo.sysdatabases
WHERE dbid > 4
Works on our SQL Server 2008
ID
5 and 6 will be ReportServer
and ReportServerTempDB
if you have SQL Server Reporting Services
installed.
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.
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
var SDBLOC = new Microsoft.SqlServer.Management.Smo.Server("localhost").Databases.Cast<Microsoft.SqlServer.Management.Smo.Database>().Where(bs => !bs.IsSystemObject && bs.ID>6).ToList();
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
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();
}
var DBsLOC = new Microsoft.SqlServer.Management.Smo.Server("localhost").Databases.Cast<Microsoft.SqlServer.Management.Smo.Database>().Where(bs => !bs.IsSystemObject && bs.ID>6).ToList();
or foreach (var Db in new Microsoft.SqlServer.Management.Smo.Server("localhost").Databases)
as in .NET 4.0 + SQL Server 2014 or .SqlServer.Smo\12.0.0.0
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.
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]
In SQL Server 7, dbid 1 thru 4 are the system dbs.
perhaps I'm a dodo!
show databases;
worked for me.
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;
To exclude system databases :
SELECT name FROM master.dbo.sysdatabases where sid <>0x01
sid
its column on the sys.databases
table its owner_sid
Success story sharing
exec sp_databases
did not work. The other two (master.dbo.sysdatabases
andsys.databases
) still work.