Get Table and Index storage size in sql server
This query here will list the total size that a table takes up - clustered index, heap and all nonclustered indices:
SELECT
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.schemas s ON s.schema_id = t.schema_id
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
WHERE
t.NAME NOT LIKE 'dt%' -- filter out system tables for diagramming
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
s.Name, t.Name
If you want to separate table space from index space, you need to use AND i.index_id IN (0,1)
for the table space (index_id = 0
is the heap space, index_id = 1
is the size of the clustered index = data pages) and AND i.index_id > 1
for the index-only space
Table and Index size in SQL Server
The exec sp_spaceused
without parameter shows the summary for the whole database. The foreachtable solution generates one result set per table - which SSMS might not be able to handle if you have too many tables.
I created a script which collects the table infos via sp_spaceused
and displays a summary in a single record set, sorted by size.
create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'
open c
fetch c into @id
while @@fetch_status = 0 begin
insert into #t
exec sp_spaceused @id
fetch c into @id
end
close c
deallocate c
select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc
drop table #t
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 do you determine the size of an index in SQL Server?
You can determine the size of all indexes on a table (and other things) like so:
EXEC sp_spaceused TableName;
For a full breakdown you may:
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('TableName'), null, null, 'DETAILED');
How to find out how much space a SQL Server table uses?
If you have SSMS, you can right-click on the table in the Object Explorer, go to Properties, and then look at the Storage page. The field, Data space, is the size of the data in that table, but it probably does not include some of the overhead costs of the table.
How do I check the size of an indexed view in SQL Server?
EXEC sys.sp_spaceused @objname = N'dbo.YourView'
SQL Server - Get approximate size of table
You can use the management studio also
Right Click on table -> Properties -> Storage
or you can use the query like this:
sp_spaceused 'TableName'
To get it for all the tables you can use it like tihs:
CREATE TABLE #tmp
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
insert #tmp
EXEC sp_MSforeachtable @cmd="EXEC sp_spaceused '?'"
select * from #tmp
Related Topics
When to Use Grouping Sets, Cube and Rollup
How to Make a Sum Without Group By
Find N Nearest Neighbors for Given Point Using Postgis
In Ms SQL Server, How to "Atomically" Increment a Column Being Used as a Counter
Sqlite Create Pre-Populated Fts Table
Select Top N Rows for Each Group
How to Get a View Table Query (Code) in SQL Server 2008 Management Studio
Connect to SQL via Windows Authentication Over Vpn
How to Create a One-Time-Use Function in a Script or Stored Procedure
Finding All Children in a Hierarchy SQL
Query SQL to Subtract Two Fields
Retrieve the Maximum Length of a Varchar Column in SQL Server
Postgresql: Check If Schema Exists
How to Force a SQL Server 2008 Database to Go Offline