Database Object Count Across Multiple Databases in SQL Server

In large SQL Server environments, managing and auditing databases can become a complex task, especially when dealing with numerous databases and a variety of objects within them. Whether you’re a database administrator (DBA) or a developer, you might often need to get a quick overview of the object distribution across all your databases. This is where automation can save you a significant amount of time and effort.

In this blog post, we’ll explore a method to dynamically count various types of objects—like tables, views, stored procedures, and more—across all user databases in your SQL Server instance. We’ll use a combination of dynamic SQL and cursors to achieve this.

Why Automate Object Counting?

• Time Efficiency: Manually counting objects in each database is time-consuming and error-prone.
• Consistency: Automation ensures that the same process is applied uniformly across all databases.
• Reporting: Easily generate reports to understand the structure and complexity of your databases.
• Monitoring: Track changes in the number of objects over time to monitor database growth or clean-up effectiveness.

The Script

Below is a comprehensive SQL script that accomplishes this task. It iterates over all online user databases, counts different types of objects, and stores the results in a temporary table.

DECLARE @SQL NVARCHAR(MAX) = N'';
DECLARE @DBName NVARCHAR(128);

-- Temporary table to store results
IF OBJECT_ID('tempdb..#ObjectCounts') IS NOT NULL
    DROP TABLE #ObjectCounts;

CREATE TABLE #ObjectCounts (
    DatabaseName NVARCHAR(128),
    ObjectType NVARCHAR(50),
    ObjectCount INT
);

-- Cursor to iterate over all databases
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE' -- Exclude offline databases
  AND name NOT IN ('master', 'tempdb', 'model', 'msdb'); -- Exclude system databases

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'USE [' + @DBName + N'];
        INSERT INTO #ObjectCounts (DatabaseName, ObjectType, ObjectCount)
        SELECT ''' + @DBName + N''', ''Tables'', COUNT(*) FROM sys.tables
        UNION ALL
        SELECT ''' + @DBName + N''', ''Views'', COUNT(*) FROM sys.views
        UNION ALL
        SELECT ''' + @DBName + N''', ''Stored Procedures'', COUNT(*) FROM sys.procedures WHERE type = ''P''
        UNION ALL
        SELECT ''' + @DBName + N''', ''Functions'', COUNT(*) FROM sys.objects WHERE type IN (''FN'', ''TF'', ''IF'')
        UNION ALL
        SELECT ''' + @DBName + N''', ''Triggers'', COUNT(*) FROM sys.triggers
        UNION ALL
        SELECT ''' + @DBName + N''', ''Indexes'', COUNT(*) FROM sys.indexes WHERE type_desc <> ''HEAP''
        UNION ALL
        SELECT ''' + @DBName + N''', ''User-Defined Types'', COUNT(*) FROM sys.types WHERE is_user_defined = 1
        UNION ALL
        SELECT ''' + @DBName + N''', ''Synonyms'', COUNT(*) FROM sys.synonyms
        UNION ALL
        SELECT ''' + @DBName + N''', ''Sequences'', COUNT(*) FROM sys.sequences
        UNION ALL
        SELECT ''' + @DBName + N''', ''Constraints'', COUNT(*) FROM sys.objects WHERE type IN (''C'', ''D'', ''F'', ''PK'', ''UQ'')
        UNION ALL
        SELECT ''' + @DBName + N''', ''Schemas'', COUNT(*) FROM sys.schemas;
    ';

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Select the results
SELECT * FROM #ObjectCounts;

-- Drop the temporary table
DROP TABLE #ObjectCounts;

How It Works

1. Variable Initialization: The script begins by declaring the necessary variables.
2. Temporary Table Creation: It creates a temporary table #ObjectCounts to store the results.
3. Cursor Declaration: A cursor is declared to iterate over all online user databases. System databases like master, tempdb, model, and msdb are excluded.
4. Dynamic SQL Construction: For each database, dynamic SQL is constructed to count different types of objects. The sp_executesql stored procedure is used to execute this dynamic SQL.
5. Result Insertion: The counts are inserted into the temporary table.
6. Cursor Operations: The cursor fetches each database name, processes it, and moves to the next until all databases are processed.
7. Cleanup: After processing all databases, the cursor is closed and deallocated, and the results are selected from the temporary table. Finally, the temporary table is dropped.

Running the Script

To run this script, simply execute it in your SQL Server Management Studio (SSMS) or any other SQL client connected to your SQL Server instance. Ensure that the executing user has the necessary permissions to access the metadata of all databases.

Conclusion

This script is a powerful tool for DBAs and developers who need to manage and audit multiple databases efficiently. By automating the object counting process, you can save time, ensure consistency, and generate valuable insights into your SQL Server environment.