[dba-VB] Get list of servers, databases and tables in a database

MartyConnelly martyconnelly at shaw.ca
Mon Jun 11 16:31:56 CDT 2007


You could use NetServerEnum API  call;  but not sure how this works over 
multiple domains
 You could change this to be a function returning
 a list of the SQL servers in a ADOR Recordset or an array etc.

 At present, it just does a debug.print of all the SQL servers on the 
network.

 http://www.codeguru.com/vb/gen/vb_system/network/article.php/c1641

Doing  it using SQLDMO may present a problem as SQL Server 2005 uses SQLSMO
and SQL DMO may not be present as they conflict or did at one point

Sub testdmo()
' need reference to SQLDMO.dll
  Dim oApp As SQLDMO.Application
        Dim oNames As SQLDMO.NameList
        Dim oName As Variant

        Set oApp = New SQLDMO.Application
        Set oNames = oApp.ListAvailableSQLServers()
        For Each oName In oNames
            'cboServers.AddItem oName
            Debug.Print oName
        Next
End Sub

You could use ADSI and WMI but may need to individually check each 
machine in domain
whicj may take some time

See http://www.databasejournal.com/features/mssql/article.php/3413691

List computers that are connected to a specific domain controller
Sub ListConnectedComputers( strDomain )
    Dim objPDC
    Set objPDC = getobject("WinNT://" & strDomain )
    objPDC.filter = Array("Computer")
    For Each objComputer In objPDC
   
    Debug.Print  "Name: " & objComputer.Name
    Next
End Sub

Dim strDomain
Do
    strDomain = inputbox( "Please enter a domainname", "Input" )
Loop until strDomain <> ""
ListConnectedComputers( strDomain )

Lists all domains in the namespace
Sub ListDomains()
    Dim objNameSpace
    Dim Domain
   
    Set objNameSpace = GetObject("WinNT:")
    For Each objDomain In objNameSpace
         Debug.Print "Name: " &  objDomain.Name
    Next
End Sub

jwcolby wrote:

>No, I mean programmatically.  Basically I am looking to populate a combo
>with servers, then when I select a server, populate a combo with databases,
>select a database, populate a combo with tables etc.
>
>I am writing an application for the import / export / import of data.  Flat
>files into SQL Server, data back out to "CSV" for address validation, data
>back in from CSV.
>
>ATM I am hard coding all the locations but eventually I must get this thing
>more automated so I am just boning up on how to browse through database
>objects.  I am looking at code right now for the SQLDMO object which looks
>like it will serve the purpose.  It is not part of .NET but I can reference
>it from my project.
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com 
>-----Original Message-----
>From: dba-vb-bounces at databaseadvisors.com
>[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
>Sent: Monday, June 11, 2007 10:55 AM
>To: dba-vb at databaseadvisors.com
>Subject: Re: [dba-VB] Get list of servers, databases and tables in a
>database
>
>View menu, Server Explorer.
>
>Charlotte 
>
>-----Original Message-----
>From: dba-vb-bounces at databaseadvisors.com
>[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
>Sent: Monday, June 11, 2007 6:49 AM
>To: dba-vb at databaseadvisors.com; dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-VB] Get list of servers, databases and tables in a
>database
>
>I forgot to specify that I am using VB.Net for this.  So I need to know how
>to do all this inside of VB.Net. 
>
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com
>-----Original Message-----
>From: dba-vb-bounces at databaseadvisors.com
>[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
>Sent: Monday, June 11, 2007 9:26 AM
>To: dba-sqlserver at databaseadvisors.com; dba-vb at databaseadvisors.com
>Subject: [dba-VB] Get list of servers, databases and tables in a database
>
>I need to allow a user (me) to navigate through my servers, databases and
>tables in databases to select a specific table, for example to export to
>CSV.  Does anyone know how to determine instances of database servers, then
>get lists of databases in those servers, then get tables in a specific
>database?
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com 
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the dba-VB mailing list