[sql] How to find the size of a table in SQL?

How to find the size of a table in SQL?

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

sp_spaceused ‘Tablename’

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

SELECT COUNT(*) FROM your_table_name

You may refer the answer by Marc_s in another thread, Very useful.

Get size of all tables in database

Here's a simple query, if you are just trying to find the largest tables.

  -- Find largest table partitions
 SELECT top 20 obj.name, LTRIM (STR ( sz.in_row_data_page_count * 8, 15, 0) + ' KB') as Size, * FROM sys.dm_db_partition_stats sz
inner join sys.objects obj on obj.object_id = sz.object_id
  order by sz.in_row_data_page_count desc

A query (modification of https://stackoverflow.com/a/7892349/1737819) to find a custom name table size in GB. You might try this, replace 'YourTableName' with the name of your table.

SELECT 
    t.NAME AS TableName,    
    p.rows AS RowCounts,
    CONVERT(DECIMAL,SUM(a.total_pages)) * 8 / 1024 / 1024 AS TotalSpaceGB, 
    SUM(a.used_pages)  * 8 / 1024 / 1024 AS UsedSpaceGB , 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 / 1024 AS UnusedSpaceGB
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 = 'YourTable'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceGB DESC, t.Name

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/

I know that in SQL 2012 (may work in other versions) you can do the following:

1. Right click on the database name in the Object Explorer.
2. Select Reports > Standard Reports > Disk Usage by Top Tables.

That will give you a list of the top 1000 tables and then you can order it by data size etc.

And in PostgreSQL:

SELECT pg_size_pretty(pg_relation_size('tablename'));

SQL Server, nicely formatted table for all tables in KB/MB:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    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 
    t.Name

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.

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