利用 "select name from sysdatabases" 语句,可以得到服务器中所有数据库名称! ‘ Select all User Tables from selected database Query = " Select * from sysobjects where xtype='U'" rs.Open Query, conn, adOpenStatic, adLockReadOnly 这里的conn是你的数据库连接
列出某服务器上所有数据库 http://community.csdn.net/Expert/topic/3086/3086911.xml?temp=7.786196E-02 Option ExplicitPrivate Const DB_PROVIDER = "SQLOLEDB" Private Const SQL_SRV = "bafony" 'SQl服务器Private cn As ADODB.Connection Private rs As ADODB.Recordset Private Sub OpenConnection() Set cn = New ADODB.Connection With cn .Provider = DB_PROVIDER '拥护名/密码 .ConnectionString = "Data Source=" & SQL_SRV & ";User ID=sa ;Password=sa" .Open End With End Sub Private Sub ListAllServerDatabases() Dim rsDB As ADODB.Recordset Dim i As Integer Combo1.Clear i = 1 Set rsDB = cn.OpenSchema(adSchemaCatalogs) While Not rsDB.EOF Combo1.AddItem rsDB!CATALOG_NAME rsDB.MoveNext i = i + 1 Wend
rsDB.Close Set rsDB = Nothing
End SubPrivate Sub Command1_Click() If Combo1.Text = "" Then MsgBox "选择数据库" Exit SubEnd If Call ListAllTables(Trim(Combo1.Text))End SubPrivate Sub Form_Load() Call OpenConnection Call ListAllServerDatabases End SubPrivate Sub ListAllTables(ByVal sDB As String) Dim rsTables As ADODB.Recordset Dim i As Integer Dim NewConnString As String
i = 1 Set rsTables = cn.OpenSchema(adSchemaTables)
While Not rsTables.EOF
If UCase(rsTables!TABLE_TYPE) = "TABLE" Then Combo2.AddItem rsTables!TABLE_NAME i = i + 1 End If
rsTables.MoveNext
Wend
rsTables.Close Set rsTables = Nothing
End Sub
Dim rs As New ADODB.Recordset Set rs = Conn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")) Do Until rs.EOF List1.AddItem rs!TABLE_NAME rs.MoveNext Loop rs.Close Conn.Close省略Array(Empty, Empty, Empty, "TABLE"),可以得到当前数据库中的所有表名(包括系统表)
Query = " Select * from sysobjects where xtype='U'"
rs.Open Query, conn, adOpenStatic, adLockReadOnly
这里的conn是你的数据库连接
http://community.csdn.net/Expert/topic/3086/3086911.xml?temp=7.786196E-02
Option ExplicitPrivate Const DB_PROVIDER = "SQLOLEDB"
Private Const SQL_SRV = "bafony" 'SQl服务器Private cn As ADODB.Connection
Private rs As ADODB.Recordset
Private Sub OpenConnection()
Set cn = New ADODB.Connection
With cn
.Provider = DB_PROVIDER
'拥护名/密码
.ConnectionString = "Data Source=" & SQL_SRV & ";User ID=sa ;Password=sa"
.Open
End With
End Sub
Private Sub ListAllServerDatabases()
Dim rsDB As ADODB.Recordset
Dim i As Integer
Combo1.Clear
i = 1
Set rsDB = cn.OpenSchema(adSchemaCatalogs)
While Not rsDB.EOF
Combo1.AddItem rsDB!CATALOG_NAME
rsDB.MoveNext
i = i + 1
Wend
rsDB.Close
Set rsDB = Nothing
End SubPrivate Sub Command1_Click()
If Combo1.Text = "" Then
MsgBox "选择数据库"
Exit SubEnd If
Call ListAllTables(Trim(Combo1.Text))End SubPrivate Sub Form_Load()
Call OpenConnection
Call ListAllServerDatabases
End SubPrivate Sub ListAllTables(ByVal sDB As String) Dim rsTables As ADODB.Recordset
Dim i As Integer
Dim NewConnString As String
Combo2.Clear
NewConnString = "Data Source=" & SQL_SRV & ";User ID=sa;Password=sa" & ";Initial Catalog=" & sDB
cn.Close
cn.Open NewConnString
i = 1
Set rsTables = cn.OpenSchema(adSchemaTables)
While Not rsTables.EOF
If UCase(rsTables!TABLE_TYPE) = "TABLE" Then
Combo2.AddItem rsTables!TABLE_NAME
i = i + 1
End If
rsTables.MoveNext
Wend
rsTables.Close
Set rsTables = Nothing
End Sub
Set rs = Conn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
Do Until rs.EOF
List1.AddItem rs!TABLE_NAME
rs.MoveNext
Loop
rs.Close
Conn.Close省略Array(Empty, Empty, Empty, "TABLE"),可以得到当前数据库中的所有表名(包括系统表)