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

ID: Q176426

The information in this article applies to:

Microsoft SQL Server versions 6.0, 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.

Additional query words: records


Keywords          : kbprg SSrvGen 
Version           : Windows:6.0,6.5
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 16, 1999