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

User · Answer

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

User · Answer

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

User · Answer

SELECT  name   FROM master dbo sysdatabases  WHERE dbid  gt  4    Works on our SQL Server 2008

User · Answer

To exclude system databases   SELECT  name  FROM master dbo sysdatabases WHERE dbid  gt  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  gt  4

User · Answer

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

User · Answer

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

User · Answer

SELECT  name   FROM master dbo sysdatabases  WHERE dbid  gt  4 and  name   lt  gt   ReportServer  and  name   lt  gt   ReportServerTempDB    This will work for both condition  Whether reporting is enabled or not

User · Answer

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  quot sp databases quot  system stored procedure  I only removed unneeded column and added where conditions

User · Answer

To exclude system databases    SELECT name FROM master dbo sysdatabases where sid  lt  gt 0x01

User · Answer

You can find all database names with this -   select name from sys sysdatabases

User · 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

User · Answer

In SQL Server 2008 R2 this works   select name  from master sys databases  where owner sid  gt  1    And list only databases created by user s

User · Answer

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  amp  amp   database IsDatabaseSnapshot             select database Name              ToArray

User · Answer

perhaps I m a dodo   show databases  worked for me

User · Answer

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

[sql-server] Get list of databases from SQL Server

Examples related to sql-server