Count Tables, Views and Stored Procedures in your Database

Back in 2010 I posted a few scripts to count your database’s tables, views and stored procedures. Good news: they still run unchanged on SQL Server 2022 and Azure SQL, because sys.objects has been the catalog view to query since SQL Server 2005. Here they are again, cleaned up, with a couple of 2026 notes.

Count Tables

SELECT COUNT(*) AS [Tables Count] FROM sys.objects WHERE type = 'U';
SELECT name AS [Table Name] FROM sys.objects WHERE type = 'U' ORDER BY name;

There is also a dedicated shortcut view, sys.tables:

SELECT COUNT(*) AS [Tables Count] FROM sys.tables;

Count Views

SELECT COUNT(*) AS [Views Count] FROM sys.objects WHERE type = 'V';
SELECT name AS [View Name] FROM sys.objects WHERE type = 'V' ORDER BY name;

The shortcut here is sys.views.

Count Stored Procedures

SELECT COUNT(*) AS [Stored Procs Count] FROM sys.objects WHERE type = 'P';
SELECT name AS [Stored Proc Name] FROM sys.objects WHERE type = 'P' ORDER BY name;

Shortcut: sys.procedures.

All three at once

SELECT type_desc, COUNT(*) AS [Count]
FROM sys.objects
WHERE type IN ('U', 'V', 'P')
GROUP BY type_desc
ORDER BY type_desc;

2026 notes

• The original scripts filtered with name NOT LIKE 'Sy%'. That filter was unnecessary and subtly wrong: the sys.* catalog views never contain system objects to begin with (those live in sys.system_objects), while a user table legitimately named SyncLog would have been silently skipped. Dropped.
• Aliases now use square brackets instead of single quotes — AS 'Tables Count' worked, but bracketed identifiers are the documented, non-deprecated form.
• If you need per-schema counts, join sys.schemas on schema_id — most real databases stopped putting everything in dbo years ago.
• Catalog view reference: sys.objects (Transact-SQL) and the catalog views overview on Microsoft Learn (both verified live).

Related posts in this SQL Server series: listing foreign-key constraints, emptying a database and resetting identity columns, checking for violated constraints, and clearing an oversized transaction log.