/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

10 votes198 views2 answers
Mysql select timestamp group by day
SQL
1 vote227 views1 answer
Flask sqlalchemy default value
SQL
1 vote241 views1 answer
Postgres see sequence current state
SQL
0 vote235 views1 answer
Max length found in mysql
SQL
0 vote212 views1 answer
How to create a new db from dumb file mysql
SQL
0 vote234 views1 answer
Mysql 5.7 in centos 7
SQL
5 votes221 views1 answer
Sort order on two columns sql
SQL
0 vote206 views1 answer
Delete double on SQL with multiple primary keys
SQL
-1 vote217 views1 answer
Mac mysql workbench console
SQL
5 votes193 views1 answer
List foreign key oracle
SQL