SQL Server 2000: how do i get a list of tables and

Question

This question already has answers here:

Query to list number of records in each table in a database (20 answers)

Closed 5 years ago.

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.

Answer 1

You can go whole hog on this one. The problem with using sysIndexes to get rowcounts is that they're not always up to date. There is a way to make them all up to date, though. The following code will give you row counts for each table and a whole bunch more.

/**********************************************************************************************************************
 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 2

how about "dtproperties" and "sysdiagrams" ? these tables will show up as user table incorrectly

Answer 3

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

Answer 4

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'