怎样读取一个数据库中的所有表的结构？（急征例程）

[netmuse]

有一个 sqlserver的库，其中有哪些表未知，表的结构未知，怎样在vb中通过编程实现读取所有的表名、表的结构、以及所有纪录？可使用vb自带的控件？

[netmuse]

DAO和ADO随便用！

[fishzone]

Private CN As ADODB.Connection
Private RS As ADODB.Recordset
Private strTableName As String '用来存放表名
Private intLastCol As String
Private intLastSort As Integer

Private Sub comOK_Click()

Dim str As String
str = "Provider=SQLOLEDB.1;User ID=" & txtSqlUserName.Text & ";PassWord=" & txtSqlPassWord.Text & ";Initial Catalog=" & txtSqlDataBase.Text & ";Data Source=" & txtSQLServerName.Text

On Error GoTo MyErr

Set CN = New ADODB.Connection
CN.Open str

Set RS = CN.OpenSchema(adSchemaTables)
cobTable.Clear
Do Until RS.EOF
If RS("TABLE_TYPE") = "TABLE" Then
cobTable.AddItem RS("TABLE_NAME")
End If
RS.MoveNext
Loop
RS.Close

Set RS = New ADODB.Recordset
Set RS.ActiveConnection = CN
RS.CursorLocation = adUseClient
RS.LockType = adLockOptimistic
RS.CursorType = adOpenStatic

Exit Sub

'通过 OpenSchema 方法可以列出所有的表名，并将它们放在 cobTable 这个 ComboBox 里。
'在 cobTable 的 Click 事件，敲入以下代码:

Private Sub cobTable_Click()
Me.MousePointer = 11
strTableName = cobTable.Text
If Not strTableName = "" Then
Dim strSql As String
On Error GoTo MyErr
strSql = "select * from " & strTableName
RS.Open strSql
Dim n As Integer
Dim m As Integer
lstvData.ListItems.Clear
lstvData.ColumnHeaders.Clear

For m = 0 To RS.Fields.Count - 1
lstvData.ColumnHeaders.Add , , RS.Fields(m).Name
Next m
'把每一个字段名加入到 lstvData 的 ColumnHeaders 。lstvData 是一个 listView 控件。

If RS.RecordCount > 0 Then

RS.MoveFirst
For n = 1 To RS.RecordCount '添加没一条纪录。
With lstvData.ListItems.Add(, , RS(RS.Fields(0).Name) & "")
For m = 1 To RS.Fields.Count - 1 '添加每一个字段。
.ListSubItems.Add , , RS(RS.Fields(m).Name) & ""
Next m
End With
RS.MoveNext
Next n
End If
labRecordCount.Caption = RS.RecordCount & " Records" '这个表的总纪录数。
RS.Close
End If
Me.MousePointer = 0
Exit Sub
MyErr:
MsgBox Err.Description, , "Error"
Err.Clear
Me.MousePointer = 0
End Sub

[fishzone]

这是我前段时间写的一个程序，
98/2000 + vb6.0 调试通过。
4个文本筐 txtSqlUserName 是 sql server 用户名；txtSqlPassWord 密码；txtSqlDataBase 是你在 sql server 的数据库名字。txtSQLServerName 是 sql server 所在的机器名或者ip。有一个 combobox 名叫 cobTable ,一个 ListView 控件名叫 LstvData。
有分吗?

[wind_lu]

Private Sub Command1_Click()
Dim cn As rdoConnection
Dim ps As rdoPreparedStatement
Dim rs As rdoResultset
Dim strConnect As String
Dim strSQL As String
'open a connection to the pubs database using DSNless connections
'change the Server argument to match your SQL Server
strConnect = "Driver={SQL Server}; Server=myserver; " & _
"Database=pubs; Uid=sa; Pwd="
Set cn = rdoEnvironments(0).OpenConnection(dsName:="", _
Prompt:=rdDriverNoPrompt, _
ReadOnly:=False, _
Connect:=strConnect)

strSQL = "Select so.name,sc.name,st.name,sc.length " & _
"FROM syscolumns sc,master..systypes st,sysobjects so " & _
"WHERE sc.id in (select id from sysobjects where type ='P')" & _
" AND so.type ='P' " & _
"AND sc.id = so.id " & _
"AND sc.type = st.type " & _
"AND sc.type <> 39"
'create a prep stmt for the stored proc call
Set ps = cn.CreatePreparedStatement("MyPs", strSQL)

Set rs = ps.OpenResultset(rdOpenStatic)

'add the first resultset to a list box
list1.AddItem "SP Name,Param Name,Data Type,Length"
While Not rs.EOF
list1.AddItem rs(0) & " , " & rs(1) & " , " & rs(2) & " , " & _
rs(3)
rs.MoveNext
Wend
'Close the resultset and the connection and set both to nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Private Sub Form_Load()
Command1.Caption = "List all Stored Procedures"
End Sub

[pandax]

大家为什么不用Adox呢，查查msdn就有结果了。