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