T-SQL Count of Tables, Views, Stored Procedures and Functions

Have you ever needed to obtain a list or count of all tables, views, stored procedures, and functions in a database? It’s quite easy. Simply copy the T-SQL from below to a query window, set the desired database and execute.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

This will produce four result sets that will list the tables, views, stored procedures and functions. You can simply update the select statements with the keyword count to grab the count of each object if that’s all you are looking for.