Get the total counts from all the tables in the source database [Answered]RSS

• • Reply
  Saraqueen

  Member

  3 Points

  3 Posts

  Get the total counts from all the tables in the source database

  Jun 12, 2019 10:45 AM|Saraqueen|LINK

  Hello all,
  How can i get the total counts of rows from all the tables in the database . I am working on sql server
• • Reply
  AddWeb Solut...

  Participant

  820 Points

  497 Posts

  Re: Get the total counts from all the tables in the source database

  Jun 12, 2019 10:55 AM|AddWeb Solution|LINK

  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.

  Ashish Jain
• • Reply
  Saraqueen

  Member

  3 Points

  3 Posts

  Re: Get the total counts from all the tables in the source database

  Jun 12, 2019 01:56 PM|Saraqueen|LINK

  Thank you Addweb. It is helping me to solve my issue