Home Forums SQL Server 2005 Development user tables in database where the number of rows is less than 100 RE: user tables in database where the number of rows is less than 100

  • January 13, 2009 at 1:02 am

    #925685

    Abhijit More (1/13/2009)

    select distinct object_name(object_id) as Table, rows

    from sys.partitions where index_id >= 1

    and object_name(object_id) not like 'sys%'

    and rows > 100

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'Table'.

    Also, yours would miss tables that had no indexes at all, count system tables whos names don't start with sys (service broker queues as an example) and miss user tables that do start with sys

    select object_name(object_id) as TableName, rows

    from sys.partitions

    where index_id in (0,1) -- heap or clustered index. No point in counting the nonclusters

    and OBJECTPROPERTY(object_id, 'IsMSShipped')=0 -- Don't want any system tables, do want user tables that may start with sys

    and rows < 100

    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