Counting Objects by Type in Every Database With One Query

Counting objects by type in every database takes one loop and one pivot. Collect the counts once per database, keep the databases you cannot open visible, and report file size separately.

The request that arrives every audit season

Someone from audit or migration planning asks a friendly question. “How many tables, views, and procedures does each database have?” You could open every database and run the same query by hand. By the fifth database you will hate this request.

A better plan is one script that visits each database and returns one row per database. To prove it works, I first plant a database with objects I know. This demo creates a database named SqlAuthorityDemo and drops it at the end.

USE master;
IF DB_ID(N'SqlAuthorityDemo') IS NOT NULL
BEGIN
    ALTER DATABASE SqlAuthorityDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SqlAuthorityDemo;
END;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.Customers (CustomerId int PRIMARY KEY, CustomerName nvarchar(100) NOT NULL);
CREATE TABLE dbo.Orders (OrderId int PRIMARY KEY, CustomerId int NOT NULL, OrderDate date NOT NULL DEFAULT (GETDATE()));
GO
CREATE VIEW dbo.CustomerNames AS SELECT CustomerId, CustomerName FROM dbo.Customers;
GO
CREATE PROCEDURE dbo.GetCustomer @CustomerId int AS
    SELECT CustomerId, CustomerName FROM dbo.Customers WHERE CustomerId = @CustomerId;
GO
CREATE FUNCTION dbo.OrderYear (@OrderDate date) RETURNS int AS BEGIN RETURN YEAR(@OrderDate); END;
GO
CREATE FUNCTION dbo.OrdersFor (@CustomerId int) RETURNS TABLE AS
    RETURN SELECT OrderId FROM dbo.Orders WHERE CustomerId = @CustomerId;
GO

That is two tables, one view, one procedure, and two functions. Now ask sys.objects what it sees. It describes objects in the current database only. The is_ms_shipped filter hides objects that SQL Server itself ships.

SELECT type, type_desc, COUNT_BIG(*) AS ObjectCount
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type, type_desc
ORDER BY type;

Surprise: there are more rows than you planned for. The primary keys show up as PK, and the default on OrderDate shows up as D. Functions arrive under two different codes, FN for scalar and IF for inline. So decide up front which codes belong in the report. Constraints should not inflate your table count.

Choose the databases to scan

I keep the list explicit. The script takes online user databases, which means database_id above 4 and state 0. It also records whether you can enter each one with HAS_DBACCESS. A database you cannot open must stay in the report with an empty count, not a fake zero.

DROP TABLE IF EXISTS #ObjectTypeCounts, #InventoryDatabases;
GO
CREATE TABLE #InventoryDatabases
(DatabaseId int PRIMARY KEY, DatabaseName sysname, Accessible bit, ErrorText nvarchar(2048) NULL);

INSERT #InventoryDatabases (DatabaseId, DatabaseName, Accessible)
SELECT database_id, name, CONVERT(bit, HAS_DBACCESS(name))
FROM sys.databases
WHERE database_id > 4 AND state = 0;

CREATE TABLE #ObjectTypeCounts (DatabaseId int, TypeCode char(2), ObjectCount bigint);

SELECT DatabaseId, DatabaseName, Accessible, ErrorText FROM #InventoryDatabases ORDER BY DatabaseId;

Collect once per database

Now the loop. It builds a small dynamic batch per database. QUOTENAME protects odd database names, and the database id travels as a parameter. A database can vanish or lock between the list and the visit, so a failure is written to ErrorText instead of stopping everything.

DECLARE @db sysname, @id int, @sql nvarchar(max);

DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT DatabaseId, DatabaseName FROM #InventoryDatabases WHERE Accessible = 1 ORDER BY DatabaseId;
OPEN dbs;
FETCH NEXT FROM dbs INTO @id, @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'USE ' + QUOTENAME(@db) + N';
        INSERT #ObjectTypeCounts SELECT @databaseId, type, COUNT_BIG(*)
        FROM sys.objects WHERE is_ms_shipped = 0 GROUP BY type;';
    BEGIN TRY
        EXEC sys.sp_executesql @sql, N'@databaseId int', @databaseId = @id;
    END TRY
    BEGIN CATCH
        UPDATE #InventoryDatabases SET ErrorText = ERROR_MESSAGE() WHERE DatabaseId = @id;
    END CATCH;
    FETCH NEXT FROM dbs INTO @id, @db;
END;
CLOSE dbs;
DEALLOCATE dbs;

Pivot the types into columns

The last query turns type codes into the columns the request asked for. Conditional aggregation is enough for a fixed list. I keep the mapping visible: U is a table, V a view, P and PC are procedures, and the function codes go together. Allocated file size comes from sys.master_files, aggregated first.

WITH Counts AS
(SELECT DatabaseId,
        SUM(CASE WHEN TypeCode = 'U' THEN ObjectCount ELSE 0 END) AS TableCount,
        SUM(CASE WHEN TypeCode = 'V' THEN ObjectCount ELSE 0 END) AS ViewCount,
        SUM(CASE WHEN TypeCode IN ('P', 'PC') THEN ObjectCount ELSE 0 END) AS ProcedureCount,
        SUM(CASE WHEN TypeCode IN ('FN', 'IF', 'TF', 'FS', 'FT', 'AF') THEN ObjectCount ELSE 0 END) AS FunctionCount
 FROM #ObjectTypeCounts
 GROUP BY DatabaseId),
Files AS
(SELECT database_id, SUM(CONVERT(bigint, size)) * 8.0 / 1024 AS AllocatedFileMB
 FROM sys.master_files
 GROUP BY database_id)
SELECT d.DatabaseName, d.Accessible, d.ErrorText,
       CASE WHEN d.Accessible = 1 AND d.ErrorText IS NULL THEN COALESCE(c.TableCount, 0) END AS TableCount,
       CASE WHEN d.Accessible = 1 AND d.ErrorText IS NULL THEN COALESCE(c.ViewCount, 0) END AS ViewCount,
       CASE WHEN d.Accessible = 1 AND d.ErrorText IS NULL THEN COALESCE(c.ProcedureCount, 0) END AS ProcedureCount,
       CASE WHEN d.Accessible = 1 AND d.ErrorText IS NULL THEN COALESCE(c.FunctionCount, 0) END AS FunctionCount,
       f.AllocatedFileMB
FROM #InventoryDatabases AS d
LEFT JOIN Counts AS c ON c.DatabaseId = d.DatabaseId
LEFT JOIN Files AS f ON f.database_id = d.DatabaseId
ORDER BY d.DatabaseName;

Find the SqlAuthorityDemo row. It shows 2 tables, 1 view, 1 procedure, and 2 functions, exactly what I planted. The other rows depend on your server, which is why I do not quote them. The CASE wrappers give an inaccessible or failed database an empty count instead of a zero.

Do not multiply by files

One database has at least two files, data and log. Join the type counts straight to sys.master_files and every count is multiplied by the number of files. This query shows the trap for the demo database.

SELECT d.DatabaseName, SUM(t.ObjectCount) AS TablesAfterFileJoin
FROM #InventoryDatabases AS d
JOIN #ObjectTypeCounts AS t ON t.DatabaseId = d.DatabaseId AND t.TypeCode = 'U'
JOIN sys.master_files AS f ON f.database_id = d.DatabaseId
WHERE d.DatabaseName = N'SqlAuthorityDemo'
GROUP BY d.DatabaseName;

Four tables instead of two, because the database has two files. That is why the report aggregates files first. Also remember that allocated file size is not used space.

Last, a warning about what this report is. A definition count is a baseline, not proof that anything is unused. A procedure that runs monthly looks idle in a short window. Before any cleanup, add usage evidence and ask the owner. Then remove the demo.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
DROP TABLE IF EXISTS #ObjectTypeCounts, #InventoryDatabases;

Count first, then ask what each object is used for.

An object count is not a usage report, it is an inventory.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.