SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

This query shows rows, columns and size for each table, so you can spot the biggest table in database.

USE DatabaseName
GO
CREATE TABLE #temp (
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 #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp 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 #temp

The shorter way, with no temporary table

The script above has served people well for years, but it works by running sp_spaceused once per table and collecting the answers. On a database with a few hundred tables that is a few hundred small queries. SQL Server already keeps these numbers, so you can read them in one go.

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 AS t
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
  AND i.object_id > 255
GROUP BY s.name, t.name, p.rows
ORDER BY TotalSpaceKB DESC;

No temporary table, no loop, and it comes back instantly even on a big database. The first row is your biggest table.

What those three numbers actually mean

This is the part nobody explains, and it is where the useful information hides.

Data is the rows themselves. Index is everything built on top to find them quickly. Unused is space the table has reserved but is not filling.

If a table’s index space is far larger than its data space, you probably have indexes nobody uses. Every one of them has to be maintained on every insert and update, so that is worth a look. And if unused space is large, the table has usually had a lot of rows deleted from it and never gave the space back.

Biggest by size is not the same as biggest by rows

Ask “which is my biggest table” and you are really asking one of two questions. A table with fifty million narrow rows and a table with two million rows carrying documents in them are big in completely different ways, and they cause different problems.

Sort by TotalSpaceKB when you are chasing disk space. Sort by RowCounts when you are chasing a slow query. The query above gives you both columns, so change the ORDER BY to suit the question you are actually asking.

The quick one liner

When you just want the row counts and nothing else, this is the one I keep in my snippets.

SELECT s.name AS SchemaName,
       t.name AS TableName,
       SUM(p.rows) AS RowCounts
FROM sys.tables AS t
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN sys.partitions AS p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY RowCounts DESC;

The index_id filter picks the heap or the clustered index, which is where the real row count lives. Without it you count the same rows once for every index and the numbers come out far too big.

Two things worth knowing

These row counts are maintained by SQL Server rather than counted on demand, so they are fast but they can drift very slightly. For finding your biggest table that does not matter at all. If you need a number that is exactly right for a report, use COUNT(*) on that one table.

The old script uses sp_msforeachtable, which Microsoft has never documented and has never promised to keep. It still works, and plenty of people rely on it, but that is why I would not put it in a script that has to run unattended for years.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.