/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

2 votes243 views1 answer
Sql server: select column values as comma separated string
SQL
7 votes233 views3 answers
Add primary key to database sql
SQL
0 vote235 views1 answer
How to write triggers to increment or decrement the number of employees
SQL
4 votes232 views3 answers
Mysql modify foreign key
SQL
1 vote238 views1 answer
Best sql course
SQL
2 votes237 views2 answers
How to create an empty table from an existing table
SQL
1 vote256 views1 answer
Postgresql where datetrunc month and year equal
SQL
9 votes224 views3 answers
Mysql get last id
SQL
24 votes265 views4 answers
Postgres delete database
SQL
2 votes228 views1 answer
Pandas to sql index
SQL