How to find largest objects in a SQL Server database?
In SQL Server 2008, you can also just run the standard report Disk Usage by Top Tables. This can be found by right clicking the DB, selecting Reports->Standard Reports and selecting the report you want.
This query help to find largest table in you are connection.
SELECT TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
I've been using this SQL script (which I got from someone, somewhere - can't reconstruct who it came from) for ages and it's helped me quite a bit understanding and determining the size of indices and tables:
SELECT
t.name AS TableName,
i.name as indexName,
sum(p.rows) as RowCounts,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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
WHERE
t.name NOT LIKE 'dt%' AND
i.object_id > 255 AND
i.index_id <= 1
GROUP BY
t.name, i.object_id, i.index_id, i.name
ORDER BY
object_name(i.object_id)
Of course, you can use another ordering criteria, e.g.
ORDER BY SUM(p.rows) DESC
to get the tables with the most rows, or
ORDER BY SUM(a.total_pages) DESC
to get the tables with the most pages (8K blocks) used.
You may also use the following code:
USE AdventureWork
GO
CREATE TABLE #GetLargest
(
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50)
)
SET NOCOUNT ON
INSERT #GetLargest
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT
a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #GetLargest a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #GetLargest