INF: How to Determine Number of Rows of Every Table in Database

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

The following script will return the name and the number of rows in every user-defined table in a given database:

   USE pubs -- replace pubs with your database name

   SET NOCOUNT ON
   DECLARE tables_cursor CURSOR
      FOR
      SELECT name FROM sysobjects WHERE type = 'U'
   OPEN tables_cursor
   DECLARE @tablename varchar(30), @quote char(1)
   SELECT @quote = '"'

   FETCH NEXT FROM tables_cursor INTO @tablename
   WHILE (@@fetch_status <> -1)
   BEGIN
      EXEC ("Select " + @quote+"Rows in " + @tablename + " = "+
                  @quote + ", count(*) from "+  @tablename)
      FETCH NEXT FROM tables_cursor INTO @tablename
   END
   DEALLOCATE tables_cursor
   SET NOCOUNT OFF

MORE INFORMATION

Notice that the "select count(*)" statement on a large table can be time consuming. Also, for additional information on every table, see the command DBCC CHECKDB in SQL Server Books Online.