利用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