Get the number of records for each table in the SQL Server database.

GetRecordCountForAllTables.sql

DECLARE @sql NVARCHAR(4000)
DECLARE @tableName VARCHAR(255);

CREATE TABLE #DatabaseTables (TableName VARCHAR (255), RecordCount INT);

DECLARE tables_cursor CURSOR FOR
    SELECT name FROM sysobjects WHERE xtype = 'U' ORDER BY name

OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'INSERT INTO #DatabaseTables (TableName, RecordCount) SELECT ''' + @tableName + ''', COUNT(1) FROM ' + @tableName;
    EXEC (@sql)
    FETCH NEXT FROM tables_cursor INTO @tableName
END

CLOSE tables_cursor;
DEALLOCATE tables_cursor;

SELECT TableName, RecordCount FROM #DatabaseTables

DROP TABLE #DatabaseTables