SQL Server Object Count Script for Each Database
This script dynamically counts the following objects in each user database of an SQL Server
instance:
Tables, Views, Stored Procedures (SP), User-defined Functions (Scalar, Inline, and Table-Valued),
Triggers, Indexes, Schemas.
DECLARE @DatabaseName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)
-- Create a table to store results
CREATE TABLE #DatabaseObjectCounts (
DatabaseName NVARCHAR(128),
TablesCount INT,
ViewsCount INT,
SPCount INT,
FunctionsCount INT,
TriggersCount INT,
IndexesCount INT,
SchemasCount INT
)
-- Get all database names except system databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND name NOT IN ('master', 'tempdb', 'model', 'msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName
-- Loop through each database
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build dynamic SQL to count objects in each database
SET @SQL = 'USE ' + QUOTENAME(@DatabaseName) + ';
INSERT INTO #DatabaseObjectCounts (DatabaseName, TablesCount, ViewsCount, SPCount, FunctionsCount,
TriggersCount, IndexesCount, SchemasCount)
SELECT
''' + @DatabaseName + ''',
(SELECT COUNT(*) FROM sys.tables),
(SELECT COUNT(*) FROM sys.views),
(SELECT COUNT(*) FROM sys.procedures),
(SELECT COUNT(*) FROM sys.objects WHERE type IN (''FN'', ''IF'', ''TF'')),
(SELECT COUNT(*) FROM sys.triggers),
(SELECT COUNT(*) FROM sys.indexes WHERE object_id IN (SELECT object_id FROM sys.tables)),
(SELECT COUNT(*) FROM sys.schemas)'
-- Execute the dynamic SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @DatabaseName
END
-- Close and deallocate the cursor
CLOSE db_cursor
DEALLOCATE db_cursor
-- Select the result from the temporary table
SELECT * FROM #DatabaseObjectCounts
-- Drop the temporary table
DROP TABLE #DatabaseObjectCounts
How the Query Works:
1. Cursor Setup: A cursor is used to loop through all user databases that are online (excluding
system databases).
2. Dynamic SQL Execution: For each database, the script switches context (USE
[DatabaseName]) and runs queries to count tables, views, stored procedures, functions,
triggers, indexes, and schemas.
3. Function Count: For counting functions, it uses sys.objects and filters based on the
function types: FN (Scalar), IF (Inline Table-Valued), and TF (Table-Valued).
4. Results Collection: The counts for each object type are inserted into a temporary table
#DatabaseObjectCounts.
5. Final Output: After iterating through all databases, the results are retrieved from the
temporary table and displayed.
