Get a list of databases from SQL Server

Option 1: SQL Connection

            Dim sqlsb As New SqlClient.SqlConnectionStringBuilder()
            sqlsb.ConnectionString = connString '_connectionString
            sqlsb.InitialCatalog = ""

            Using conDB As New SqlConnection(sqlsb.ToString())
                conDB.Open()
                dtDatabases = conDB.GetSchema("Databases")
                conDB.Close()
                '   *** cmbDatabase.Items.Clear()
                For Each r As DataRow In dtDatabases.Rows
                    Select Case r("database_name").ToString
                        Case "master", "tempdb", "msdb", "model"
                        Case Else
                            ' **** mbDatabase.Items.Add(r("database_name"))
                            names.Add(r("database_name"))
                    End Select
                Next
            End Using

Option 2: SQL Select:

SELECT DB_NAME(database_id) AS [Database], database_id
FROM sys.databases
WHERE database_id>4

Option 3: Microsoft SMO Objects

Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 140.17199.0


var SDBLOC = new Microsoft.SqlServer.Management.Smo.Server("localhost").Datab‌​ases.Cast<Microsoft.‌​SqlServer.Management‌​.Smo.Database>().Whe‌​re(bs => !bs.IsSystemObject && bs.ID>6).ToList();

Advertisements