SQL: Count number of records in each table within a database

The Transact-SQL script below 1 (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.

USE [database name]
GO

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
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.