Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server 2012 New Spatial Instantiable Data Types
SQL Server Helper
Home > > Tip of the Day
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

Back to Tip of the Day List Next Tip