弱电论坛 › 服务器 › 利用SQL语句得到某数据库中用户数据表清单

东东 发表于 2011-3-12 20:35:50

利用SQL语句得到某数据库中用户数据表清单

'1、利用SQL语句得到某数据库中用户数据表清单
<br />&#39;语法：SQL="select * from sysobjects where xtype=&#39;u&#39;"
<br />Sub cx13()
<br />&nbsp; &nbsp; Dim cnn As New ADODB.Connection
<br />&nbsp; &nbsp; Dim rs As New ADODB.Recordset
<br />&nbsp; &nbsp; Dim cnnstr As String, sql As String, mydata As String, i As Long
<br />&nbsp; &nbsp; &#39;On Error Resume Next
<br />&nbsp; &nbsp; mydata = "NHDP_CZSW"
<br />&nbsp; &nbsp; cnnstr = "Provider=SQLOLEDB;" _
<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;& "User ID=sa;" _
<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;& "Password=;" _
<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;& "Data Source=ZGH;" _
<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;& "initial catalog=" & mydata
<br />&nbsp; &nbsp; cnn.ConnectionString = cnnstr
<br />&nbsp; &nbsp; cnn.Open
<br />&nbsp; &nbsp; sql = "select * from sysobjects where xtype=&#39;u&#39;"&nbsp; &nbsp; &#39;固定语法，死记硬背之^_^ xtype=&#39;u&#39;指用户表
<br />&nbsp; &nbsp; Set rs = cnn.Execute(sql)
<br />&nbsp; &nbsp; With Sheets("数据库清单")
<br />&nbsp; &nbsp; &nbsp; &nbsp; .Cells.Clear
<br />&nbsp; &nbsp; &nbsp; &nbsp; &#39;复制表头信息
<br />&nbsp; &nbsp; &nbsp; &nbsp; For i = 0 To rs.Fields.Count - 1
<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Cells(1, i + 1) = rs.Fields(i).Name
<br />&nbsp; &nbsp; &nbsp; &nbsp; Next i
<br />&nbsp; &nbsp; &nbsp; &nbsp; .Range("a2").CopyFromRecordset rs
<br />&nbsp; &nbsp; End With
<br />&nbsp; &nbsp; rs.Close
<br />&nbsp; &nbsp; cnn.Close
<br />&nbsp; &nbsp; Set rs = Nothing
<br />&nbsp; &nbsp; Set cnn = Nothing
<br />End Sub

查看完整版本: 利用SQL语句得到某数据库中用户数据表清单