typescript获取数据库数据_VBA实例:获取数据库所有表及其字段分拆

最近给客户代写的一个需求，分享给大家。

需求场景：读取数据库的所有表和字段名，并将字段横向分拆到多列。

如需获取工程文件，请私信给我邮箱。

----获取数据库所有表名及字段XML Path

Sub connsql()

  Dim strConn As String, strSQL As String

  Dim conn As ADODB.Connection

  Dim ds As ADODB.Recordset

  Dim col As Integer

  Dim i, j, m, n As Integer

  Dim arr

  '连接数据库的字符串

   strConn = "Provider=SQLOLEDB;Server=" & UserForm1.TextBox1.Text & ";Database=" & UserForm1.TextBox2.Text & ";Uid=" & UserForm1.TextBox3.Text & ";Pwd=" & UserForm1.TextBox4.Text & ";"

   Debug.Print (strConn)

  '查询语句，如果sql语句很长可以用strSQL=strSQL+来连接分成多段的语句，如果语句很短可以只写在一行上。

   strSQL = "select table_name,filed_name=STUFF((select ',',+ filed_name from (select * from (SELECT b.name as table_name,a.name as filed_name FROM SysColumns a" + _

    " left join (select * from sys.objects where type='U') b on a.id=b.object_id) t where t.table_name is not null) t1 where t1.table_name=t2.table_name for xml path('')),1,1,'')" + _

    " from (select * from (SELECT b.name as table_name,a.name as filed_name FROM SysColumns a left join (select * from sys.objects where type='U') b on a.id=b.object_id) t where t.table_name is not null)  t2 group by table_name"

   Set conn = New ADODB.Connection

   Set ds = New ADODB.Recordset

   conn.ConnectionString = strConn

   conn.Open

   If conn.State = 1 Then

        MsgBox ("连接成功!")

   Else

        MsgBox ("数据源连接失败，请检查！")

   End If

   ds.Open strSQL, conn, adOpenStatic, adLockOptimistic

   Range("A1").Value = "表名"

   Range("A2").CopyFromRecordset ds

   conn.Close

   For i = 2 To Range("A65536").End(xlUp).Row

        Range("B" & i).Replace "", ""

        Range("B" & i).Replace "", ""

   Next

  Call splitcol

End Sub

---拆分字段

Sub splitcol()

      Dim i, j, m, n As Integer

      For i = 2 To Range("A65536").End(xlUp).Row

        arr = Split(Range("B" & i).Value, ",")

        n = 0

        For m = 2 To UBound(arr) + 2

            Cells(i, m) = arr(n)

            n = n + 1

        Next

    Next

End Sub

更多Excel与统计分析资料，请扫码关注：Excel与统计分析