Dynamic Record Counting Script
Understanding data spread across tables is essential for analytics and maintenance in large databases.
The following T-SQL script counts records in each table of a specified schema and sorts the results by record count in descending order.
DECLARE @SchemaName NVARCHAR(255) = 'dbo'; -- Set the schema name here
DECLARE @CurrentTableName NVARCHAR(255); -- The name of the current table being processed
DECLARE @DynamicSQL NVARCHAR(MAX); -- Dynamic SQL command to be constructed and executed
DECLARE @ResultsTable TABLE (TableName NVARCHAR(255), RecordCount INT); -- Table to store results
-- Cursor to iterate through all tables in the specified schema
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = @SchemaName;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @CurrentTableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Construct the SQL query to count records in the current table
SET @DynamicSQL = 'SELECT ''' + @CurrentTableName + ''' AS TableName, COUNT(*) AS RecordCount FROM [' + @SchemaName + '].[' + @CurrentTableName + ']';
-- Insert the result into the @ResultsTable
INSERT INTO @ResultsTable (TableName, RecordCount)
EXEC sp_executesql @DynamicSQL;
FETCH NEXT FROM TableCursor INTO @CurrentTableName;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;
-- Select final results, sorted by RecordCount in descending order
SELECT * FROM @ResultsTable ORDER BY RecordCount DESC;Example Output
Key Points
- Cursors: Loop through tables in the schema and process them individually.
- Dynamic SQL: Build and execute SQL queries for each table dynamically.
- Temporary Table: Store and sort record counts for efficient presentation.
Concluding Insights
This script is a straightforward tool for database analytics, providing quick insights into table record counts.
Modify the @SchemaName variable to fit your needs.
The code is available on GitHub
Be cautious when using it on a production database, as performance may be impacted, especially with large tables or high concurrency.