how to find size of table in SQL?

Question

how to find size of table in SQL?

Answer 1

SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes… which might surprise you.

Syntax:

 sp_spaceused ‘Tablename’

see in :

http://www.howtogeek.com/howto/database/determine-size-of-a-table-in-sql-server/

Answer 2

Determing SQL Server Table Size

http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx

Answer 3

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

Answer 4

And in PostgreSQL:

SELECT pg_size_pretty(pg_relation_size('tablename'));

Answer 5

Do you by size mean the number of records in the table, by any chance? In that case:

SELECT COUNT(*) FROM your_table_name

Answer 6

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.