TSQL – Count SQL Objects by Types

database_object_by_type_count_TSQL

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: http:\\www.abcdata.ca
-- Create date	: 2018-03-28
-- Description	: Count SQL Objects by Type
-- =================================================
select			case 	type
							when	'AF'	then	'Aggregate function (CLR)'
							when	'C'		then	'CHECK constraint'
							when	'D'		then	'DEFAULT (constraint or stand-alone)' 
							when	'F'		then	'FOREIGN KEY constraint' 
							when	'FN'	then	'SQL scalar function' 
							when	'FS'	then	'Assembly (CLR) scalar-function' 
							when	'FT'	then	'Assembly (CLR) table-valued function' 
							when	'IF'	then	'SQL inline table-valued function'
							when	'IT'	then	'Internal table'
							when	'P'		then	'SQL Stored Procedure'
							when	'PC'	then	'Assembly (CLR) stored-procedure'
							when	'PG'	then	'Plan guide'
							when	'PK'	then	'PRIMARY KEY constraint'
							when	'R'		then	'Rule (old-style, stand-alone)'
							when	'RF'	then	'Replication-filter-procedure'
							when	'S'		then	'System base table'
							when	'SN'	then	'Synonym'
							when	'SO'	then	'Sequence object'
							when	'SQ'	then	'Service queue'
							when	'TA'	then	'Assembly (CLR) DML trigger'
							when	'TF'	then	'SQL table-valued-function'
							when	'TR'	then	'SQL DML trigger'
							when	'TT'	then	'Table type'	
							when	'U'		then	'Table (user-defined)'
							when	'UQ'	then	'UNIQUE constraint'
							when	'V'		then	'View'
							when	'X'		then	'Extended stored procedure'
							else							'Unknow'
						end									as	Object
					, count (*)						as	#
from				sys.objects
group by	  type
order by	  type;