Easy way to find out how many rows in total are stored within SQL Server Database?

Question

I'm looking for easy way to count all rows within one SQL Server 2005/2008 database (skipping the system tables of course)? I know i could use

SELECT COUNT (COLUMN) FROM TABLE

and do it for each table and then add it up but would prefer some automated way?

Is there one?

Answer 1

You could query sysindexes, look at the rowcnt value for the clustered index on each table. But I'm not sure exactly how up to date that is.

Alternatively, something like this (briefly tested on a small test db):

CREATE TABLE #TableRowCount (TableName NVARCHAR(128), RowCnt BIGINT)
INSERT #TableRowCount (TableName, RowCnt) 
EXECUTE sp_msforeachtable 'SELECT "?", COUNT(*) FROM ?'
SELECT SUM(RowCnt) AS TotalRowCount FROM #TableRowCount
DROP TABLE #TableRowCount

Answer 2

I'm not sure if older version of MS SQL has the information_shema SQL standard for data dictionary.

You can do something like:

SELECT SUM(table_rows)
FROM  information_schema.tables
WHERE table_schema = 'DATABASENAME'

Answer 3

Try:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects AS so, 
    sysindexes AS si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

This is the indexed rows. This is probably only an approximation, as databases change a lot and some stuff might not be indexed, but this will be fast.

EDIT: Note that so.xtype is user types, making the assumption you do not want the system stuff and only "real" data stuff.

EDIT2: no flames note: probably a bad idea to query on the sysobjects table :).

EDIT3: to specifically address requirement, and no associative joins :)

SELECT sum(mycount) from
(SELECT 
    MAX(si.rows) AS mycount
FROM 
    sysobjects AS so 
    join sysindexes AS si  on si.id = OBJECT_ID(so.name) 

WHERE 
    so.xtype = 'U' 
GROUP BY 
  so.name 
) as mylist

Answer 4

We know that sp_spaceused, when passed a table name, will return a row count, so we can examine what it does - it queries sys.dm_db_partition_stats - and copy it to get this:

SELECT
    SUM(ddps.row_count) TotalRows
FROM
    sys.indexes i
    INNER JOIN sys.objects o ON i.OBJECT_ID = o.OBJECT_ID
    INNER JOIN sys.dm_db_partition_stats ddps ON 
        o.OBJECT_ID = ddps.OBJECT_ID
        AND i.index_id = ddps.index_id
WHERE
    i.index_id < 2
    AND o.is_ms_shipped = 0 -- to exclude system tables

Curious requirement though, I have to say...

Answer 5

This was my answer to a similar question today:

SQL Server 2005 or later gives quite a useful report showing table sizes - including row counts etc. It's in Standard Reports - and it is Disc Usage by Table.

Programmatically, there's a nice solution at: http://www.sqlservercentral.com/articles/T-SQL/67624/

Answer 6

Check out the undocumented stored procedure sp_MSForEachTable. Using it you can run a Count(*) on every table in the database. For your specific issue:

EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'

Answer 7

SELECT SUM(row_count)
    FROM sys.dm_db_partition_stats
    WHERE index_id IN (0,1)
    AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0;

This will be accurate except for, potentially, any rows that are being added or removed within a transaction at the time you run the query. And it won't have the expense of hitting individual tables.

But as I mentioned in another comment, I'm not sure how this helps you determine "how much data" your database holds. How many rows, sure, but if I have 10 glasses, each half full of water, and you have 5 glasses, each completely full, which of us has more water?