How to count each record in each table in a SQL Server 2005 Database?

Question

I need to know how many records are in each table in a particular database. I don't care what is in the record, they will all need to be counted.

I already know how to do this for a single table as SELECT Count(1) FROM [my_table_name], but this gets a little redundant for 200+ tables.

Answer 1

Try this: (Very fast method though potentially less precise.)

SELECT   so.name, MAX(si.rows) 
FROM     sysobjects so
JOIN     sysindexes si ON so.xtype = 'U' 
AND      si.id = OBJECT_ID(so.name) 
GROUP BY so.name 
ORDER BY 2 DESC

That's one method of many. See here for several other options.

Answer 2

From How do I get a list of SQL Server tables and their row counts?

DECLARE @SQL VARCHAR(255) 
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
EXEC(@SQL) 

CREATE TABLE #foo 
( 
    tablename VARCHAR(255), 
    rc INT 
) 

INSERT #foo 
    EXEC sp_msForEachTable 
        'SELECT PARSENAME(''?'', 1), 
        COUNT(*) FROM ?' 

SELECT tablename, rc 
    FROM #foo 
    ORDER BY rc DESC 

DROP TABLE #foo 

Answer 3

As has already been posted, if it was a one off thing I'd probably use sp_msforeachtable. However, it's an undocument system procedure so if that's a problem, then you can easily do it like this:

DECLARE @SQL VARCHAR(MAX)
SET @SQL = ''

SELECT @SQL = 
    @SQL + 'SELECT ''' + TABLE_NAME + ''' AS TableName, COUNT(*) AS RecordCount 
            FROM [' + TABLE_NAME + '];' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES

EXECUTE (@SQL)

Answer 4

I'm sure you can get something better than this but this should do in a pinch. For the record, sp_spaceused is your friend in queries such as this!

DECLARE @tbls table (TableID int identity, TableName varchar(max))

INSERT INTO @tbls
SELECT t1.name
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1

DECLARE @tblcount int

SELECT @tblcount=COUNT(1)
FROM @tbls

DECLARE @results table (name varchar(max), [rows] bigint, reserved varchar(max), data varchar(max), index_size varchar(max), unused varchar(max))

DECLARE @counter int
SET @counter = 0
DECLARE @tblname varchar(max)

while @counter < @tblcount
begin
    SELECT @tblname=TableName FROM @tbls WHERE TableID=@counter+1

    INSERT INTO @results
    exec sp_spaceused @tblname

    SET @counter = @counter+1
end

SELECT * FROM @results