sql-server - 如何获取视图列表及其大小?
问题描述
这个查询给了我一个包含它们大小的表格列表。如何更改它,以便它给我一个 VIEWS 列表?
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
解决方案
视图可以有索引。但也许不是。
SELECT
t.NAME AS TableName,
s.Name AS SchemaName
FROM
sys.views t
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
GROUP BY
t.Name, s.Name
ORDER BY
t.Name
您可以将其用于带有索引的视图。但我推荐左外连接:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.views t
LEFT OUTER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
LEFT OUTER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT OUTER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
--AND t.is_ms_shipped = 0
--AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
推荐阅读
- google-oauth - idpiframe_initialization_failed with People Api
- swiftui - 包裹在 UIViewRepresentable 中的自定义 UITextField 会干扰 NavigationView 中的 ObservableObject
- javascript - 使用 Material-table Reactjs 映射数据
- python - torch.einsum 'RuntimeError:操作数 0 的尺寸不匹配:方程 4 张量 2'
- r - R:基于跨三个不同列的 mtaching 值合并两个数据帧
- vba - 如果数据被 VBA 更改,则记录已更改错误
- javascript - 带有 materialUI TextField 表单的 react-hook-form 控制器导致 TypeError: e.target is undefined when using onChange
- mongodb - 如何根据字段将mongodb集合拆分为多个集合
- javascript - React redux 使用不同的调度更新单个属性与更新整个对象
- git - 推送到多个 GitHub 存储库