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?

Solution 1

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?

Solution 2

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/

Solution 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

Solution 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...

View more solutions

Comments

[AdaTheDev]

Unfortunately that's not in MS SQL :(

[MadBoy]

I was asked the question by management board since authorities wanted to know how much data we hold in our database.

[Yada]

It's available in MS SQL 2008. msdn.microsoft.com/en-us/library/ms186778.aspx

[MadBoy]

It works, although it looks like it runs multiple queries giving me a lot of output which i will have to manually add up afterward. Other presented options seems more reasonable.

[Adir D]

The number of rows you store does very little to tell you "how much data" you hold. If one table has 240,000 rows but it is only one column wide, and another table has 1000 rows but has 240 columns, how much data is there?

[Adir D]

This information is readily available in the DMVs - it can be very expensive to run a cursor performing COUNT(*) against every single table. Also note that sp_msforeachtable is undocumented and unsupported, so its behavior could change (or be removed altogether) at any moment.

[MadBoy]

I know that. But government agency specifically asked us about row count of our database. I guess it's for their statistics to compare it with other financial institutions. If it's correct question it's not my problem :-)

[Adir D]

In addition to provide a set instead of a number, this is really not an efficient way to solve the problem, IMHO.

[Adir D]

Also see my comments to @AdaTheDev.

[MadBoy]

Thanks, this seems like a simplest query with lowest hit. I also granted some points to others who provided equally good information, but the winner could be only one.

[AdaTheDev]

True. I got the impression/assumed this was more a one-off task which reflects in the approach suggested. Agree that DMVs are a nicer way to go though!

[George M Reinstate Monica]

In server 2012, right-click on the table, look at the properties, pick 'storage' tab and you'll get the row count.