Distinct DB Name - Table row counts

  • Hi all,

    I am running the below query and I need to the table row counts for EACH database on the server but I can't seem to make the database name not REPEAT for each table:

    SELECT DISTINCT

    --[servername] = @@servername,

    [DatabaseName] = sd.name,

    [TableName] = so.name,

    [RowCount] = MAX(si.rows)

    FROM

    sysobjects so,

    sysindexes si,

    master..sysdatabases sd

    WHERE

    so.xtype = 'U'

    AND

    si.id = OBJECT_ID(so.name)

    GROUP BY

    sd.name, so.name

    ORDER BY

    1,2, 3 DESC

    ie...

    Servername

    Database Name1

    TableName = rows

    TableName = rows

    Database Name2

    TableName = rows

    TableName = rows

    etc...

    Can anyone help with this coding?

    Thanks...Michelle

  • 1) What for?

    2) If there is no database name what will tell you which database this particular table belongs to?

    _____________
    Code for TallyGenerator

  • what I mean is, i am getting back the SAME TABLES list for each database but these tables are not actually in the database. how do i make the query list only the tables that are actually in the database???

  • I see a join from sysindexes to sysobjects: si.id = OBJECT_ID(so.name)

    BTW, quite, mmm..., not reasonable. It must be just si.id = so.id.

    But where is your join to sysdatabases?

    _____________
    Code for TallyGenerator

  • Sysobjects - sysindexes joins on 'id'...which join should I use to include sysdatabases?

    I still have not been able to get the correct results..I need the follow:

    Dbname, Tblname, rowcnt

  • I think you should not include sysdatabases. Why would you do it?

    Sysobjects and sysindexes are in each database; that means, your query always works only with tables from the CURRENT database. If I understood what you are trying to do, this could be what you need:

    SELECT

    db_name() [DatabaseName],

    so.name [TableName],

    si.rows [RowCount]

    FROM sysobjects so

    JOIN sysindexes si ON si.id = so.id

    WHERE so.xtype = 'U'

    AND si.indid IN (0,255) /*see BOL - 0 and 255 mean that it is a table (not index)*/

    ORDER BY so.name DESC

    This way there will be no duplicates, unless you have several tables with same name but different owner. What do you want to do if this happens?

    There is undocumented procedure sp_MSforeachdb that could help you if you want to run the same code in all databases. However, I never used it myself so I can't explain how to do it properly.

  • Thank you very much...

    Regards...Michelle

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

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