Count the number of records in each table within a database

The Transact-SQL script below (written for SQL Server 2000, but also works with 2005), returns a list of tables and a count of the number of records within each table.

DECLARE @name VARCHAR(500)
DECLARE @sql VARCHAR(1000)

CREATE TABLE #TableRecordCount
( TableName VARCHAR(100),
  RecordCount INT
)

DECLARE dbCursor CURSOR FAST_FORWARD
FOR
    SELECT T.NAME
    FROM SYSOBJECTS T
    WHERE T.XTYPE = 'U'
    ORDER BY T.NAME

OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @sql = 'INSERT INTO #TableRecordCount (TableName, RecordCount) '
      SET @sql = @sql + 'SELECT ''' + @Name + ''' AS TableName, COUNT(*) AS CNT '
      SET @sql = @sql + 'FROM [' + @name + ']'
      EXEC (@sql)
      FETCH NEXT FROM dbCursor INTO @name
END

CLOSE dbCursor
DEALLOCATE dbCursor

SELECT * FROM #TableRecordCount

DROP TABLE #TableRecordCount