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

SUMMARY

   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