Abhijit More (1/13/2009)
select distinct object_name(object_id) as Table, rowsfrom 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