sql script for getting row counts for each table per DB

  • I am trying to develop script for getting table names, row count for each table. I want to do do it for each database. But I am not sure where I can find information.

    I can use db_name() to get current DB. But I want to the build query to loop through each db.

    May be, there is some system view from which I can take information to join. not sure about it

    I am trying to get following result set:

    DB-name, table-name, table-owner,row counts

    Please suggest...

    Thanks

  • this has been the fastest way I've found, because it's using index tables to count rows, rather than actually running a statemtn.

    that's important to note, because if you have a heap table with no index, it will show no rows, and wouldn't be accurate.

    stick this procedure in MASTER, and simply use the msForEach below:

    ALTER PROCEDURE sp__CountTableRows

    AS

    BEGIN

    SELECT db_name(),sysobjects.name, MAX(sysindexes.rows) AS NUMROWS

    FROM sysobjects

    INNER JOIN sysindexes ON sysobjects.id = sysindexes.id

    WHERE sysobjects.xtype = 'U'

    GROUP by sysobjects.name

    ORDER BY NUMROWS DESC,sysobjects.name

    END

    GO

    sp_msforeachdb 'USE ? exec sp__CountTableRows'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Okay this is similar to Lowell's solution, but I'll put it out there anyway:

    sp_msforeachdb @command1 = 'Select

    ''?'' as db,

    S.name as schemaname,

    T.name as tablename,

    P.rows

    from

    sys.partitions P Join

    sys.tables T On

    P.object_Id = T.object_id Join

    sys.schemas S On

    T.schema_id = S.schema_id

    Where

    P.index_id in (0,1)'

    The Where clause takes the row count only from the HEAP (0) or Clustered Index (1).

  • hi there,

    I am getting each DB name in result. but not the table names.

    It shows me name of only those tables against which I am running query. For example: if the query is run against Master DB, though I get different Db name, I get same tables for each Db set...

  • you need to make sure your msForEach has a USE statement:

    sp_msforeachdb 'USE ? exec sp__CountTableRows'

    Jack's Script needed to explicitly use the ? variable for the dbname"

    sp_msforeachdb @command1 = 'Select

    ''?'' as db,

    S.name as schemaname,

    T.name as tablename,

    P.rows

    from

    ?.sys.partitions P Join

    ?.sys.tables T On

    P.object_Id = T.object_id Join

    ?.sys.schemas S On

    T.schema_id = S.schema_id

    Where

    P.index_id in (0,1)'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • gotcha!

  • Lowell,

    Thanks for fixing my code too! I always forget that one because I always think it changes the context, but it doesn't.

  • i know what you mean;

    I wrote this neat function that returns the DDL of a table, along with it's indexes....you know, CREATE TABLE.....

    works fine in any database, but of course I wanted to just stick it in MASTER and be done with it...but when called from anotehr db, it would either not find the table passed to the proc, or return teh table in the MASTER table, and not the db i was calling from.

    ended up making it a procedure , which has no problems with references like that, but I fiddled with it for a long time, lost in guessing how to make the function work;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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