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

sql sql-server rowcount
vijaysylvester picture vijaysylvester · Feb 8, 2010 · Viewed 657.7k times · Source

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 SERVER.

Could someone suggest a sample script?

Answer

adrianbanks picture adrianbanks · Feb 8, 2010

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.

Related questions

SQL Server batch delete using WHILE loop does not work

I have a very large table, so I am using the following to delete older entries: WHILE (@@ROWCOUNT > 0) BEGIN DELETE TOP (5000) FROM myTable WHERE date < 20130103 END I have run this a few times using different dates. Sometimes it …

Read More
CTE, ROW_NUMBER and ROWCOUNT

I am trying to return a page of data and also row count of all data in one stored procedure which looks like following: WITH Props AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber FROM Property WHERE PropertyType = @…

Read More
Total Row Count in sql query---sql server 2008

My query is as follows BEGIN WITH MyCTE AS ( SELECT T.MusicAlbumTitle ,D.musicTitle ,D.mVideoID ,D.musicFileName ,T.ReleaseDate AS ReleasedDate ,D.MusicLength ,D.musicSinger ,D.MusicVideoID ,D.ExternalLink ,D.CoverImg ,ROW_NUMBER() OVER ( PARTITION BY D.MusicVideoID ORDER …

Read More