会python真的可以为所欲为0(回)
332天前
这里还有人吗1(回)
517天前这里还有人吗0(回)
517天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
741天前到处都是羊，不想上班0(回)
821天前
鸽子
0(回)
826天前BEGIN TRY |
SELECT a3.NAME AS [schemaname] |
,a2.NAME AS [tablename] |
,a1.rows AS row_count |
,(a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS reserved |
,a1.data * 8 AS data |
,( |
CASE |
WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data |
THEN (a1.used + ISNULL(a4.used, 0)) - a1.data |
ELSE 0 |
END |
) * 8 AS index_size |
,( |
CASE |
WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used |
THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used |
ELSE 0 |
END |
) * 8 AS unused |
,CASE |
WHEN a1.data = 0 |
THEN 0 |
ELSE ( |
CASE |
WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used |
THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used |
ELSE 0 |
END |
) * 10000 / (a1.reserved + ISNULL(a4.reserved, 0)) |
END unusedRate |
FROM ( |
SELECT ps.object_id |
,SUM(CASE |
WHEN (ps.index_id < 2) |
THEN row_count |
ELSE 0 |
END) AS [rows] |
,SUM(ps.reserved_page_count) AS reserved |
,SUM(CASE |
WHEN (ps.index_id < 2) |
THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) |
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) |
END) AS data |
,SUM(ps.used_page_count) AS used |
FROM sys.dm_db_partition_stats ps |
GROUP BY ps.object_id |
) AS a1 |
LEFT OUTER JOIN ( |
SELECT it.parent_id |
,SUM(ps.reserved_page_count) AS reserved |
,SUM(ps.used_page_count) AS used |
FROM sys.dm_db_partition_stats ps |
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) |
WHERE it.internal_type IN ( |
202 |
,204 |
) |
GROUP BY it.parent_id |
) AS a4 ON (a4.parent_id = a1.object_id) |
INNER JOIN sys.all_objects a2 ON (a1.object_id = a2.object_id) |
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) |
WHERE a2.type <> N'S' |
AND a2.type <> N'IT' |
ORDER BY unusedRate DESC |
END TRY |
|
BEGIN CATCH |
SELECT - 100 AS l1 |
,1 AS schemaname |
,ERROR_NUMBER() AS tablename |
,ERROR_SEVERITY() AS row_count |
,ERROR_STATE() AS reserved |
,ERROR_MESSAGE() AS data |
,1 AS index_size |
,1 AS unused |
END CATCH |
