SELECT name,
CASE ( TYPE ) 
WHEN ‘P’ THEN ‘Stored Procedure’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘V’ THEN ‘View’
WHEN ‘U’ THEN ‘Table’
WHEN ‘FN’ THEN ‘Function’
WHEN ‘F’ THEN ‘Foregin Key’
WHEN ‘K’ THEN ‘Key’
END AS ObjectType
FROM sysObjects
WHERE type IN (‘P’,‘TR’,‘V’,‘U’,‘FN’,‘F’,‘K’)
ORDER BY type,
name

SELECT
	name,
	CASE WHEN type=‘P’ THEN 
		‘Stored Procedure’
	ELSE
		CASE WHEN type=‘TR’ THEN 
			‘Trigger’ 
			ELSE CASE WHEN type=‘V’ THEN
				‘View’
				ELSE CASE WHEN type=‘U’ THEN
				‘Table’
					ELSE CASE WHEN type=‘FN’ THEN
						‘Function’
						ELSE CASE WHEN type=‘F’ THEN
						‘Foregin Key’
							ELSE CASE WHEN type=‘K’ THEN
							‘Key’
							END
						END
					END
				END
			END
		END
	END
FROM 
	sysObjects 
WHERE 
	type=‘P’ OR type=‘TR’ OR type=‘V’ OR type=‘U’ OR type=‘FN’ OR type=‘F’ OR type=‘K’ 
ORDER BY
	type, name