Search code examples
sqlsql-serverrowcount

How to fetch the row count for all tables in a SQL SERVER database


I am searching for a SQL Script that can be used to determine if there is any data (i.e. row count) in any of the tables of a given database.

The idea is to re-incarnate the database in case there are any rows existing (in any of the database).

The database being spoken of is Microsoft SQL Sserver.

Could someone suggest a sample script?


Solution

  • The following SQL will get you the row count of all tables in a database:

    CREATE TABLE #counts
    (
        table_name varchar(255),
        row_count int
    )
    
    EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
    SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
    DROP TABLE #counts
    

    The output will be a list of tables and their row counts.

    If you just want the total row count across the whole database, appending:

    SELECT SUM(row_count) AS total_row_count FROM #counts
    

    will get you a single value for the total number of rows in the whole database.