/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

1 vote320 views2 answers
Populate sql table with random data
SQL
0 vote341 views1 answer
Sql server select record with max id
SQL
0 vote337 views1 answer
Incorrect string value: '\xC4\x81ne' for column
SQL
1 vote386 views1 answer
Oracle drop temporary table
SQL
4 votes366 views2 answers
Raise errors.NotSupportedError( mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported
SQL
0 vote314 views1 answer
Mysql select row with min date
SQL
0 vote358 views2 answers
Generate sql trigger through ef migration
SQL
0 vote319 views2 answers
Clear window pl sql
SQL
0 vote305 views1 answer
Opensuse status MySQL
SQL
0 vote295 views1 answer
Jooq finding lastest value in table
SQL