Table Row Counts

  • Hi

    Is there a single query that will list the table row counts for all the databases in an instance?

  • No, but you can query sys.partitions to get the rows per table in a database, and use something like sp_MSForEachDB to loop through all databases and execute the same query.

    John

  • As each database is a container on itself, you need to loop into every database. One easy way to do it is with the sp_foreachdb shared in here: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

    There's one included which is undocumented, but it might have some problems.

    Then you just insert your results from the following query into a table to get the row counts of every table:

    SELECT DB_NAME() DBName,

    OBJECT_NAME(object_id) AS tableName,

    SUM(row_count) Row_Count

    FROM sys.dm_db_partition_stats

    WHERE index_id IN (0,1)

    GROUP BY object_id;

    NOTE: Don't forget to include the schema if needed.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Another alternative is to construct a dynamic UNION ALL query by SELECTing from sys.databases.

    Just a quick example of how that might look if my goal is to see all tables ordered by row count:

    DECLARE @sql NVARCHAR(max)='';

    SELECT @sql=@sql+' UNION ALL

    SELECT database_name='''+QUOTENAME(name)+''',

    schema_name=s.name,

    table_name=t.name,

    row_count=SUM(p.rows)

    FROM '+QUOTENAME(name)+'.sys.partitions p

    INNER JOIN

    '+QUOTENAME(name)+'.sys.tables t ON p.object_id=t.object_id

    INNER JOIN

    '+QUOTENAME(name)+'.sys.schemas s ON t.schema_id=s.schema_id

    WHERE p.index_id IN (0,1)

    GROUP BY t.name,

    s.name

    '

    FROM sys.databases

    WHERE state_desc='ONLINE';

    SET @sql=STUFF(@sql,1,10,'')+' ORDER BY row_count DESC;';

    EXEC (@sql);

    Cheers!

  • Thanks for the help guys. Check out this blog which gave me what i needed.

    http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/

  • jdbrown239 (8/26/2016)


    Thanks for the help guys. Check out this blog which gave me what i needed.

    http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/%5B/quote%5D

    Read the article that I mentioned before, here's an extract to show why it's important:

    I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • OK will do.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply