How Can I Efficiently Count Rows in All SQL Server Database Tables?

Efficiently Determining Row Counts in SQL Server Tables

Database administrators frequently need to check data volume within their SQL Server databases. This article provides a solution for identifying tables with non-zero row counts, which may trigger database re-initialization.

The following SQL script offers a streamlined approach:

<code class="language-sql">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;</code>

This script uses sp_MSForEachTable to iterate through each table. For every table, it counts the rows and inserts the table name and row count into the temporary table #counts.

The results, ordered by table name and row count (descending), provide a clear overview of data distribution.

To calculate the total number of rows across the entire database, add this statement:

<code class="language-sql">SELECT SUM(row_count) AS total_row_count FROM #counts;</code>

This efficiently summarizes the overall database size. This script empowers database administrators to effectively monitor data volume, identify empty tables, and inform database maintenance and re-initialization decisions.

The above is the detailed content of How Can I Efficiently Count Rows in All SQL Server Database Tables?. For more information, please follow other related articles on the PHP Chinese website!