怎样读取一个数据库中的所有表的结构?(急征例程)

netmuse 2001-07-05 04:28:12
有一个 sqlserver的库,其中有哪些表未知,表的结构未知,怎样在vb中通过编程实现读取所有的表名、表的结构、以及所有纪录?可使用vb自带的控件?
...全文
52 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
pandax 2001-07-05
大家为什么不用Adox呢,查查msdn就有结果了。
回复
wind_lu 2001-07-05
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
回复
fishzone 2001-07-05
这是我前段时间写的一个程序,
98/2000 + vb6.0 调试通过。
4个文本筐 txtSqlUserName 是 sql server 用户名;txtSqlPassWord 密码;txtSqlDataBase 是你在 sql server 的数据库名字。txtSQLServerName 是 sql server 所在的机器名或者ip。有一个 combobox 名叫 cobTable ,一个 ListView 控件名叫 LstvData。
有分吗?
回复
fishzone 2001-07-05
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


回复
netmuse 2001-07-05
DAO和ADO随便用!
回复
相关推荐
发帖

1187

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2001-07-05 04:28
社区公告
暂无公告