Count the Number of Rows in All Tables in a SQL Server Database

If you're working with SQL Server databases, you might often find yourself needing to quickly get the count of rows for each table within a database. This can be helpful for various tasks such as data analysis, debugging, or performance tuning. In this article, we will cover a SQL script that will help you to fetch the number of rows in all the tables in a SQL Server database.

 -- Switch to your database

USE YourDatabaseName; -- Replace 'YourDatabaseName' with the name of your database

-- Declare variables

DECLARE @TableName AS VARCHAR(255)

DECLARE @SQL AS NVARCHAR(MAX)

-- Create a temporary table to store the results

CREATE TABLE #TableCounts (

    TableName VARCHAR(255),

    RecordCount INT

)

-- Create a cursor to loop through all the tables

DECLARE TableCursor CURSOR FOR 

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'BASE TABLE'

-- Open the cursor and fetch the first table into @TableName

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

-- Loop through all tables to get the row count

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @SQL = 'INSERT INTO #TableCounts (TableName, RecordCount) SELECT ''' + @TableName + ''', COUNT(*) FROM ' + @TableName

    EXEC sp_executesql @SQL

    FETCH NEXT FROM TableCursor INTO @TableName

END

-- Close and deallocate the cursor

CLOSE TableCursor

DEALLOCATE TableCursor

-- Display the results

SELECT * FROM #TableCounts

-- Drop the temporary table

DROP TABLE #TableCounts