get-table-size.sql(файл создан)
| @@ -0,0 +1,30 @@ | |||
| 1 | + | // for TFB Database Business Central | |
| 2 | + | ||
| 3 | + | SELECT | |
| 4 | + | t.NAME AS TableName, | |
| 5 | + | s.Name AS SchemaName, | |
| 6 | + | p.rows AS RowCounts, | |
| 7 | + | SUM(a.total_pages) * 8 AS TotalSpaceKB, | |
| 8 | + | SUM(a.used_pages) * 8 AS UsedSpaceKB, | |
| 9 | + | (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, | |
| 10 | + | CAST(SUM(a.total_pages) * 8 / 1024.0 / 1024.0 AS DECIMAL(18, 2)) AS TotalSpaceGB, | |
| 11 | + | CAST(SUM(a.used_pages) * 8 / 1024.0 / 1024.0 AS DECIMAL(18, 2)) AS UsedSpaceGB, | |
| 12 | + | CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0 / 1024.0 AS DECIMAL(18, 2)) AS UnusedSpaceGB | |
| 13 | + | FROM | |
| 14 | + | sys.tables t | |
| 15 | + | INNER JOIN | |
| 16 | + | sys.indexes i ON t.OBJECT_ID = i.object_id | |
| 17 | + | INNER JOIN | |
| 18 | + | sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id | |
| 19 | + | INNER JOIN | |
| 20 | + | sys.allocation_units a ON p.partition_id = a.container_id | |
| 21 | + | LEFT OUTER JOIN | |
| 22 | + | sys.schemas s ON t.schema_id = s.schema_id | |
| 23 | + | WHERE | |
| 24 | + | t.NAME NOT LIKE 'dt%' | |
| 25 | + | AND t.is_ms_shipped = 0 | |
| 26 | + | AND i.OBJECT_ID > 255 | |
| 27 | + | GROUP BY | |
| 28 | + | t.Name, s.Name, p.Rows | |
| 29 | + | ORDER BY | |
| 30 | + | TotalSpaceGB DESC; | |
Новее
Позже