SQL Server Helper - Tip of the Day

There are cases as a database administrator that you have a need to know the exact number of rows each of your user tables contain.  Here’s a script that can become handy that counts the number of rows for each of the user tables in a database:

DECLARE cTables CURSOR LOCAL FOR
    SELECT [Name]
    FROM [sys].[tables]
 
DECLARE @TableName   VARCHAR(50)
DECLARE @SQLCommand   NVARCHAR(2000)
DECLARE @RowCount   INT
DECLARE @TableRowCount TABLE (
    [TableName]    VARCHAR(50),
    [RowCount]    INT
)
OPEN cTables
FETCH NEXT FROM cTables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLCommand = 'SELECT @RowCount = COUNT(*) FROM [dbo].[' + @TableName + ']'
    EXECUTE [sp_executesql] @SQLCommand, N'@RowCount INT OUT', @RowCount OUT
 
    INSERT INTO @TableRowCount ( [TableName], [RowCount] )
    VALUES ( @TableName, @RowCount )
 
    FETCH NEXT FROM cTables INTO @TableName
END
CLOSE cTables
DEALLOCATE cTables
SELECT * FROM @TableRowCount
ORDER BY [TableName]
GO