/SQL/Check the size of the tables here ordered from the biggest size

Check the size of the tables here ordered from the biggest size

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
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

Similar codes

0 vote206 views1 answer
Kill slow queries mysql
SQL
1 vote215 views2 answers
Mysql grant all privileges on *.* to 'root'@' ' identified by
SQL
0 vote220 views1 answer
Renommer base sql
SQL
11 votes244 views2 answers
Sql rank
SQL
0 vote223 views1 answer
Inserting values with beekeeper get error null value in column createdAt violates not-null constraint
SQL
0 vote199 views1 answer
Difference between min and max date sql
SQL
-2 votes258 views1 answer
Trunc in sql
SQL
12 votes193 views4 answers
Install mysql ubuntu 18.04
SQL
10 votes192 views4 answers
Join with multiple conditions sql
SQL
22 votes185 views2 answers
Mysql show all tables
SQL