SQL Server 2000: how to get a list of tables and row count?

Question

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 change this to get the second column with the current number of rows for the tables. I'm at least 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 that the above is not valid T-SQL, but I think he understands what I would like to do. This is for SQL Server 2000. I would prefer not to use storage procedures, if at all possible.

Answer 1

Quick and dirty way (includes uncommitted changes and possibly forwarding pointers to heaps)

 select o.name, rows from sysindexes i join sysobjects o on o.id=i.id where indid < 2 and type='U' 

Answer 2

  exec sp_MSforeachtable 'select count (*) as nr_of_rows,' '?' 'as table_name from?' 

Answer 3

On this you can go for a whole pig. The problem with using sysIndexes to get rowcounts is that they are not always relevant. However, there is a way to make them all modern. The following code will give you the number of rows for each table and the whole group.

 /********************************************************************************************************************** Purpose: Returns a single result set similar to sp_Space used for all user tables at once. Notes: 1. May be used as a view, stored procedure, or table-valued function. 2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info. Revision History: Rev 00 - 22 Jan 2007 - Jeff Moden - Initital creation for SQL Server 2000 Rev 01 - 11 Mar 2007 - Jeff Moden - Add automatic page size determination for future compliance Rev 02 - 05 Jan 2008 - Jeff Moden - Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name **********************************************************************************************************************/ --===== Ensure that all row counts, etc is up to snuff -- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should -- execute the command below prior to retrieving from the view or UDF. DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS --===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more SELECT DBName = DB_NAME(), --SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000 SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005 TableName = so.Name, TableID = so.ID, MinRowSize = MIN(si.MinLen), MaxRowSize = MAX(si.XMaxLen), ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB, DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB + SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB, IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB - SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB - SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB, UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB - SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB, Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END), RowModCtr = MIN(si.RowModCtr), HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END), HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END) FROM dbo.SysObjects so, dbo.SysIndexes si, (--Derived table finds page size in KB according to system type SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte FROM Master.dbo.spt_Values WHERE Number = 1 --Identifies the primary row for the given type AND Type = 'E' --Identifies row for system type ) pkb WHERE si.ID = so.ID AND si.IndID IN (0, --Table w/o Text or Image Data 1, --Table with clustered index 255) --Table w/ Text or Image Data AND so.XType = 'U' --User Tables AND PERMISSIONS(so.ID) <> 0 GROUP BY so.Name, so.UID, so.ID, pkb.PageKB ORDER BY ReservedKB DESC 

Answer 4

what about dtproperties "and" sysdiagrams "? these tables will not display correctly as a user table