count number of rows in every table 1

[collierd2]

Hello

Using SQL 2000

What's the easiest way to list each table along with the record count within a specific database

Thanks

 

[Jamfool]

SELECT o.name, i.rowcnt
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE indid < 2 and (OBJECTPROPERTY(object_id(o.name), N'IsTable')) = 1
order by 2 desc

 

[collierd2]

fantastic, thanks

 

[bborissov]

Just an addition to Jamfool, if you want to see only YOUR tables you could use IsMsShipped property:
SELECT o.name, i.rowcnt
FROM sysindexes i
join sysobjects o on i.id = o.id
HERE indid < 2 AND
(OBJECTPROPERTY(object_id(o.name), N'IsTable')) = 1 AND
(OBJECTPROPERTY(object_id(o.name), N'IsMsShipped')) = 0
order by 2 desc
[/code]


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.