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

user_146383 0
Viewed 732107

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?

rowcount sql-server sql
13 Answers

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.

user_1169230
user_2273170

I would make a minor change to Frederik's solution. I would use the sp_spaceused system stored procedure which will also include data and index sizes.


declare c_tables cursor fast_forward for 
select table_name from information_schema.tables 

open c_tables 
declare @tablename varchar(255) 
declare @stmt nvarchar(2000) 
declare @rowcount int 
fetch next from c_tables into @tablename 

while @@fetch_status = 0 
begin 

    select @stmt = 'sp_spaceused ' + @tablename 

    exec sp_executesql @stmt

    fetch next from c_tables into @tablename 

end 

close c_tables 
deallocate c_tables
user_1502780

select all rows from the information_schema.tables view, and issue a count(*) statement for each entry that has been returned from that view.

declare c_tables cursor fast_forward for
select table_name from information_schema.tables

open c_tables
declare @tablename varchar(255)
declare @stmt nvarchar(2000)
declare @rowcount int
fetch next from c_tables into @tablename

while @@fetch_status = 0
begin

    select @stmt = 'select @rowcount = count(*) from ' + @tablename

    exec sp_executesql @stmt, N'@rowcount int output', @rowcount=@rowcount OUTPUT

    print N'table: ' + @tablename + ' has ' + convert(nvarchar(1000),@rowcount) + ' rows'

    fetch next from c_tables into @tablename

end

close c_tables
deallocate c_tables
user_557740
Related