Listing all tables in a database and their row counts and sizes

Category : Tips User Rating : 4.5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




To get the number of rows in a single table we can use the COUNT(*) or COUNT_BIG(*) functions, e.g.
SELECT COUNT(*) FROM Sales.Customer
This is quite straightforward for a single table, but quickly gets tedious if there are a lot of tables. Here are a few ways of listing all the tables that exist in a database together with the number of rows they contain.

Using sp_MSForEachTable

This script uses a stored procedure that is undocumented by Microsoft, though it is widely known about. It will run a SQL statement against all tables in a database. As I want a single recordset, rather than one recordset for every table I’ve inserted the results into a temporary table first. I’ve also used the COUNT_BIG function which returns a bigint and was introduced in SQL Server 2000, if you know your rowcounts won’t exceed an int or are using an earlier version then use COUNT(*) instead. The code is :
CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))

EXEC sp_MSForEachTable 'INSERT INTO #RowCounts
                        SELECT COUNT_BIG(*) AS NumberOfRows,
                        ''?'' as TableName FROM ?'

SELECT   TableName,NumberOfRows
FROM     #RowCounts
ORDER BY NumberOfRows DESC,TableName

DROP TABLE #RowCounts
One downside of this approach is that if the table sizes are large the code can end up table scanning and take a long time to complete.

Using sysindexes

There is a catalog view that will give us this information. The benefit of this approach is that it uses figures that have already been calculated by SQL Server so it should run very quickly. Here is the script :
SELECT      T.name TableName,i.Rows NumberOfRows
FROM        sys.tables T
JOIN        sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE       indid IN (0,1)
ORDER BY    i.Rows DESC,T.name
The script uses the clustered index to source the information (where indid=1). Tables that don’t have a clustered index are stored as a heap but still have a row in sysindexes (where indid=0) containing the rowcount. There is one important caveat, though, the figures may not be entirely accurate ! This is because SQL Server doesn’t always keep this bang up to date. Fortunately there is a DBCC command which updates these figures. Just run this command before the script above to make sure the figures are accurate :
DBCC UPDATEUSAGE(0)

Using sp_spaceused

sp_spaceused without parameters displays the disk space reserved and used by the whole database. However by specifying a table name as the first parameter it will display the number of rows, disk space used and reserved by a table. We can use this with the sp_MSForEachTable procedure mentioned above to get results for every table. An advantage to this approach is that it also shows the space used each table (data and index).
CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),     
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes
Here is the result of the above query when run against the AdventureWorks database :

List table row count and sizes

As with the previous approach it may be necessary to run 'DBCC UpdateUsage' to ensure that the results are accurate (this can also be achieved by specifying 'updateusage' as the 2nd parameter to the stored procedure).
Link back to this article : http://www.sqlmatters.com/Articles/Listing all tables in a database and their row counts and sizes.aspx

Keywords

TSQL,table,rowcount,@@rowcount


Comments
Post by Dau on Thu 17 Jan 2013 19:26. Report Inappropriate Post

Great Tip!

I used another tip to format the number of rows and size for readability.

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
unused VARCHAR(18))

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

; WITH TABLES_ROWS_AND_SIZE AS
(
SELECT TableName
, NumberOfRows = CONVERT(bigint,rows)
, SizeinKB = CONVERT(bigint,left(reserved,len(reserved)-3))
FROM #RowCountsAndSizes
)
select TableName
, NumRows = REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,NumberOfRows),1), '.00','')
, SizeKB = REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,SizeinKB),1), '.00','')
FROM TABLES_ROWS_AND_SIZE
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes
Post by nithya on Thu 22 Jan 2015 13:22. Report Inappropriate Post

SELECT top 10
t.NAME AS TableName,

(SUM(a.total_pages) * 8)/1024.0/1024.0 AS TotalSpaceGB

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
ORDER BY
TotalSpaceGB desc
Post by JoshKC on Tue 17 Mar 2015 20:32. Report Inappropriate Post
Website : http://stackoverflow.com/questions/1443704/query-t
I'd just like to start off by saying that I'm not the original author of this but I've used it over and over again. So I thought I might post it here since this came up when searching for the original SQL



SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
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, p.[Rows]
ORDER BY
object_name(i.object_id)
Post by praveen on Tue 11 Aug 2015 05:32. Report Inappropriate Post

how can i find that how many tables created thhrough sql
Post by Skip Cherniss on Mon 28 Sep 2015 16:12. Report Inappropriate Post
Website : http://www.highspeed.me
Great Article. Thank you for taking the time to share this
Post by Geoff Johnson on Thu 19 Nov 2015 01:35. Report Inappropriate Post

Thanks for this query. It is exactly what I need for a maintenance solution. I had never heard of MSForEachTable, and I will definitely use that over and over.
Post by sandy on Thu 26 Nov 2015 06:27. Report Inappropriate Post

Thanks a lot for this query................
Post by Parminder Singh on Thu 03 Dec 2015 15:26. Report Inappropriate Post

I am not Master SQL Developer but recently stumbled upon a situation in SSIS to count the total rows
This is good for small tables--> SELECT COUNT(*) FROM Sales.Customer
But in our case for 450 million rows, developer had a requirement to count using sql query in the script task and I suggested
SELECT rowcnt AS NumberOfRows
FROM sysindexes
WHERE indid IN (0,1) --— heap or cluster
AND id = OBJECT_ID('Table')
or Exec sp_spaceused 'Table'
Which not only avoid LOCKS but gives you instant output.

Might be helpful for someone who uses typical Count(*), Count(1) etc.

Happy Coding!

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered