locked
Get the total counts from all the tables in the source database RRS feed

  • Question

  • User23738171 posted
    Hello all,
    How can i get the total counts of rows from all the tables in the database . I am working on sql server
    Wednesday, June 12, 2019 10:45 AM

Answers

  • User-1038772411 posted

    Hello saraqueen,

    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.

    Below are the highlights of this approach:

    1. This is an iterative approach which captures the row count for each of the individual tables, puts them together and displays the results for all the tables.
    2. sp_MSforeachtable is an undocumented system stored procedure.
    3. This approach can be used for testing purposes but it is not recommended for use in any production code. sp_MSforeachtable is an undocumented system stored procedure and may change anytime without prior notification from Microsoft.

    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.

    Please refer the below link

    1 ) https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database/2221898#2221898

    2 ) https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

    I hope this will help you.

    Thank you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 12, 2019 10:55 AM

All replies

  • User-1038772411 posted

    Hello saraqueen,

    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.

    Below are the highlights of this approach:

    1. This is an iterative approach which captures the row count for each of the individual tables, puts them together and displays the results for all the tables.
    2. sp_MSforeachtable is an undocumented system stored procedure.
    3. This approach can be used for testing purposes but it is not recommended for use in any production code. sp_MSforeachtable is an undocumented system stored procedure and may change anytime without prior notification from Microsoft.

    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.

    Please refer the below link

    1 ) https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database/2221898#2221898

    2 ) https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

    I hope this will help you.

    Thank you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 12, 2019 10:55 AM
  • User23738171 posted
    Thank you Addweb. It is helping me to solve my issue
    Wednesday, June 12, 2019 1:56 PM