How to Find the Size of a Table in SQL

Get size of all tables in database

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

How to measure table size in GB in a table in SQL

-- Measures tables size (in kilobytes)
-- Tested in MS SQL Server 2008 R2

declare @t table (
name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100)
)
declare @name nvarchar(100)

declare tt cursor for
Select name from sys.tables
open tt

fetch next from tt into @name
while @@FETCH_STATUS = 0
begin
  insert into @t
  exec sp_spaceused @name
  fetch next from tt into @name
end

close tt
deallocate tt

select name as table_name, [rows] as rows_count, data + [index] as total_size, data as data_size, [index] as index_size
from (select name,
[rows],
cast (LEFT(data, LEN(data)-3) as int) data,
cast (LEFT(index_size, LEN(index_size)-3) as int) [index]
 from @t
) x
order by 3 desc, 1

Determine row size for table

Another way, run this then look at MaximumRecordsize

dbcc showcontig ('YourTableNameHere') with tableresults

Query to determine the size of tables in a database? (mysql)

Replace "schema_name" with the name of one of your databases.

And use single-quotes for string literals, not double-quotes.

How can I determine the data size of each table?

You can get a report with Table size in SQL Server Management Studio (SSMS)
Select you database, then
Reports>Standard Reports>Disk Usage By Table

---

Related Topics