SQL Server 2000: how do i get a list of tables and the row counts?
28,376
A quick and dirty way (includes uncommitted changes and possibly forwarding pointers on heaps)
select o.name, rows
from sysindexes i join sysobjects o on o.id=i.id
where indid < 2 and type='U'
Comments
-
Justin808 over 3 years
I know that I can get a list of tables with
SELECT TABLE_NAME FROM information_schema.tables WHERE NOT TABLE_NAME='sysdiagrams' AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE= 'BASE TABLE'
But I'm not sure how to modify that to get a 2nd column with the current count of rows for the tables. I though of something like this:
DECLARE @tbl VARCHAR(200) (SELECT @tbl = TABLE_NAME, TABLE_NAME, (SELECT COUNT(ID) AS Cnt FROM @tbl) FROM information_schema.tables WHERE NOT TABLE_NAME='sysdiagrams' AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE= 'BASE TABLE')
I know the above is not valid T-SQL but I think it gets the point of what I would like the have done. This is for SQL Server 2000. I would prefer not to use store procedures if at all possible.
-
Justin808 about 13 yearsthis produces a separate query for each table, not a single query with all the information.
-
a_horse_with_no_name about 13 years@Justin808: yes, but it will return accurate row counts
-
Justin808 about 13 yearsTrue, in my situation the approximate count all in 1 table works is acceptable and less work (for me). it is good to know how to get the accurate counts though.