Tag: object type

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; 

Lien Permanent pour cet article : http://www.abcdata.ca/abc/?p=47

TSQL – Listing SQL Objects by Type

database_object_by_type_get_TSQL

A simple script to list sql objects by types :

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

Lien Permanent pour cet article : http://www.abcdata.ca/abc/?p=44