Post reply

Select count on sysindexes using a date where condition

  • March 9, 2011 at 2:47 pm

    #246713

    Does anyone know the code to be able to get the sysindexes rowcount with a where clause on a date? I had it saved somewhere before, and I can't find it. Here's what I've got so far, but can't figure out how to join the column to sysindexes, syscolumns, etc.

    I would like to get a resultset similar to ModifiedDate <=01/01/2000 in the table Person.Address.

    Use adventureworks;

    SELECT

    i.rowcnt

    FROM sysindexes as i

    INNER JOIN sysobjects AS o ON i.id = o.id

    and i.id = OBJECT_ID('person.Address')

    WHERE i.indid < 2

    AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

    and Person.Address.ModifiedDate<=01/01/2000

    Any help greatly appreciated!

    ¤ §unshine ¤

  • March 9, 2011 at 11:03 pm

    #1296894

    Sysindexes and sysobjects are deprecated and should not be used any longer. The replacement is sys.objects and sys.indexes. For row counts, see sys.partitions

    That said, it (as well as sysindexes) is a total row count in the table. If you have a where clause on to actual table, you'll need Count(*) the table and not retrieve the row count from the system tables

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 10, 2011 at 7:44 am

    #1297102

    Ok. Thank you!

    ¤ §unshine ¤

Viewing 3 posts - 1 through 2 (of 2 total)

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