How to find the size of a table in SQL?

How to find the size of a table in SQL?

Solution 1:

SQL Server:-

sp_spaceused 'TableName'

Or in management studio: Right Click on table -> Properties -> Storage

MySQL:-

SELECT table_schema, table_name, data_length, index_length FROM information_schema.tables

Sybase:-

sp_spaceused 'TableName'

Oracle:- how-do-i-calculate-tables-size-in-oracle

Solution 2:

Combining the answers from ratty's and Haim's posts (including comments) I've come up with this, which for SQL Server seems to be the most elegant so far:

-- DROP TABLE #tmpTableSizes
CREATE TABLE #tmpTableSizes
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert #tmpTableSizes
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"


select  * from #tmpTableSizes
order by cast(LEFT(reservedSize, LEN(reservedSize) - 4) as int)  desc

This gives you a list of all your tables in order of reserved size, ordered from largest to smallest.