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