1187
社区成员
怎样读取一个数据库中的所有表的结构？（急征例程）
有一个 sqlserver的库，其中有哪些表未知，表的结构未知，怎样在vb中通过编程实现读取所有的表名、表的结构、以及所有纪录？可使用vb自带的控件？
...全文
当前发帖距今超过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
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。
有分吗?
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
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随便用！
STM32 单片机管脚详细说明 硬件工程师初学者宝典-单片机管脚注释（图片+文字） 基于STM32F103单片机进行各个管脚复用功能释意，便于初级工程师单片机入门设计，避免设计踩坑，并且帮助工程师把UART,I2C,SWD,ADC,PWM,SDIO等功能管脚进行汇总整理，不用再浪费时间找管脚。
