excel - 使用 ADO 从 Excel 2003 执行 "sp_MSforeachtable"(SQL Server 2008 R2)

Question

我想分析我的数据库，并通过执行 "sp_MSforeachtable 'EXECUTE sp_spaceused [?];';" 将每个表的结果放入 MS Excel 工作表中。从 ADODB 命令或记录集，然后使用 CopyFromRecordset 将结果输出到 Excel。

这是我正在使用的代码:

Sub analyseHermesDB()
Dim oConn As Object, oRec As Object, oField As Object, iIndx As Integer

' sp_MSforeachtable 'EXECUTE sp_spaceused [?];';
Set oConn = CreateObject("ADODB.Connection")
Set oRec = CreateObject("ADODB.Recordset")

oConn.Open "FILE NAME=" & ThisWorkbook.Path & "\conn.udl"
    With oRec
        .activeconnection = oConn
        .Source = "sp_MSforeachtable 'EXECUTE sp_spaceused [?];';"
        .cursorlocation = 3
        .Open
        For iIndx = 0 To .fields.Count - 1
            ActiveCell.Offset(0, iIndx).Value = .fields(iIndx).Name
        Next
        Do Until .EOF
            ActiveCell.Offset(.absoluteposition, 0).CopyFromRecordset oRec
            .movenext
        Loop
        ActiveCell.Offset(0, 1).Value = .RecordCount
        .Close
    End With

End Sub

问题是:当我执行 "sp_MSforeachtable 'EXECUTE sp_spaceused [?];';"在 SQL Server 2008 Management Studio 中，每个表都有一个结果集。

我只从使用 ADO 的调用中返回一个记录集

我究竟做错了什么？我应该尝试获取一组记录集，还是执行 sp_MSforeachtable获取表列表然后执行 EXECUTE sp_spaceused [?];'每一个人？

Answer 1

sp_MSforeachtable 返回多个结果集，您需要以这种方式使用它们

但是，您可以使用一些简单的 SQL 来实现您想要的
无论如何，sp_spaceused 在内部使用类似的 SQL...

随意调整，这是我使用的脚本的精确复制/粘贴

SELECT
    o.name,
    SUM(ps.reserved_page_count)/128.0 AS ReservedMB, 
    SUM(ps.used_page_count)/128.0 AS UsedMB
FROM
    sys.objects o
    JOIN
    sys.dm_db_partition_stats ps ON o.object_id = ps.object_id
WHERE
    OBJECTPROPERTYEX(o.object_id, 'IsMSShipped') = 0
GROUP BY
    o.name
ORDER BY
    SUM(ps.reserved_page_count) DESC