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

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.