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!