只讨论 ADO 方 法 1: 引用 ADO Dim adoConnectionX As New ADODB.Connection Dim adoSchemaRecordsetX As New ADODB.Recordset 'Ms SQL 7: adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS" 'Access 2000: 'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Set adoSchemaRecordsetX = adoConnectionX.OpenSchema(adSchemaTables) Do until adoSchemaRecordsetX.EOF Debug.Print "Table name: " & _ adoSchemaRecordsetX !TABLE_NAME & vbCr & _ "Table type: " & adoSchemaRecordsetX!TABLE_TYPE & vbCr adoSchemaRecordsetX.movenext Loop 方 法 2(引 用 Microsoft ADO Extensions 2.1 for DDL and Security (ADOX)): ( 需 要 升 级 至 VB 6.0 SP3+) Dim adoConnectionX As New ADODB.Connection 'Ms SQL 7: adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS" 'Access 2000: 'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Dim adoxCatalogX As New ADOX.Catalog Set adoxCatalogX.ActiveConnection = adoConnectionX
Dim adoxTableX As ADOX.Table For Each adoxTableX In adoxCatalogX.Tables debug.print adoxTableX.name Next
在SQL Server7中可通过SELECT * FROM sysobjects WHERE xtype = 'U' OR xtype = 'S' OR xtype='V'来获得所有表(包括系统表、用户表、视图)名
'表结构,字段信息 Dim adoConnection As New ADODB.Connection Dim CatalogX As New ADOX.Catalog adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\NWind97.mdb;Persist Security Info=False" 'adoConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=sa;Initial Catalog=northwind;Data Source=" Set CatalogX.ActiveConnection = adoConnection Dim i As Integer Dim j As Integer Dim k As Integer For i = 0 To CatalogX.Tables.Count - 1 If CatalogX.Tables.Item(i).Type = "TABLE" Then For j = 0 To CatalogX.Tables.Item(i).Columns.Count - 1 Debug.Print "[" & CatalogX.Tables.Item(i).Name & "].[" & CatalogX.Tables.Item(i).Columns(j).Name & "] " & CatalogX.Tables.Item(i).Columns(j).Type Next j End If Next i
如果你用的是SQL SERVER7以上的话,请试试这个:Select * from sysobjects where type='U',这句话会列出数据库中所有用户定义的表名。 至于其结构嘛……,关注。
用法:sp_columns 'tableName'。
方 法 1: 引用 ADO
Dim adoConnectionX As New ADODB.Connection
Dim adoSchemaRecordsetX As New ADODB.Recordset
'Ms SQL 7:
adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"
'Access 2000:
'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Set adoSchemaRecordsetX = adoConnectionX.OpenSchema(adSchemaTables)
Do until adoSchemaRecordsetX.EOF
Debug.Print "Table name: " & _
adoSchemaRecordsetX !TABLE_NAME & vbCr & _
"Table type: " & adoSchemaRecordsetX!TABLE_TYPE & vbCr
adoSchemaRecordsetX.movenext
Loop
方 法 2(引 用 Microsoft ADO Extensions 2.1 for DDL and Security (ADOX)):
( 需 要 升 级 至 VB 6.0 SP3+)
Dim adoConnectionX As New ADODB.Connection
'Ms SQL 7:
adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"
'Access 2000:
'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Dim adoxCatalogX As New ADOX.Catalog
Set adoxCatalogX.ActiveConnection = adoConnectionX
Dim adoxTableX As ADOX.Table
For Each adoxTableX In adoxCatalogX.Tables
debug.print adoxTableX.name
Next
Dim adoConnection As New ADODB.Connection
Dim CatalogX As New ADOX.Catalog
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\NWind97.mdb;Persist Security Info=False"
'adoConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=sa;Initial Catalog=northwind;Data Source="
Set CatalogX.ActiveConnection = adoConnection
Dim i As Integer
Dim j As Integer
Dim k As Integer
For i = 0 To CatalogX.Tables.Count - 1
If CatalogX.Tables.Item(i).Type = "TABLE" Then
For j = 0 To CatalogX.Tables.Item(i).Columns.Count - 1
Debug.Print "[" & CatalogX.Tables.Item(i).Name & "].[" & CatalogX.Tables.Item(i).Columns(j).Name & "] " & CatalogX.Tables.Item(i).Columns(j).Type
Next j
End If
Next i
至于其结构嘛……,关注。