Aug 202010

I needed to count all of the rows in an entire database.

I looked around and came up with a pretty easy way to do this.

Create this stored procedure and when you run it, it will display it for you.

create proc AllTableCount AS BEGIN /*Create temp table to populate the row counts for all of the tables*/ CREATE TABLE #TableRowCount( TableName sysname, [RowCount] int) /*Now actually get the counts*/ EXEC sp_MSforeachtable 'INSERT #TableRowCount (TableName,[RowCount]) SELECT ''?'',Count(*) from ?' /*Finally: sum up all of the counts and show it*/ Select SUM([RowCount]) as [Total Number Of Rows From Database] from #TableRowCount END

  • You could also resort to @precommand and @postcommand parameters so that everything be managed by the sp_MSforeachtable:

    EXEC sp_MSforeachtable @precommand = ‘CREATE TABLE ##TableRowCount(TableName sysname, [RowCount] int);’,
    @command1 = ‘INSERT ##TableRowCount (TableName, [RowCount]) SELECT ”?”, COUNT(*) FROM ?;’,
    @postcommand = ‘SELECT TableName, [RowCount] FROM ##TableRowCount ORDER BY TABLENAME COMPUTE SUM([RowCount]); DROP TABLE ##TableRowCount;’