SQL server query to search object name in database

When you create any new object(table, stored procedure, view,...etc.) in database then same object name will be stored in system table Sysobjects.

Following query returns all the object names along with object type from database where object name contains the value 'empl'.

SELECT Name,
    CASE xtype 
	WHEN 'U'  THEN 'Table'
	WHEN 'C'  THEN 'CHECK constraint' 
	WHEN 'D'  THEN 'DEFAULT constraint'
	WHEN 'F'  THEN 'FOREIGN KEY constraint' 
	WHEN 'FN' THEN 'Scalar function'
	WHEN 'IT' THEN 'Internal table'
	WHEN 'P'  THEN 'Stored procedure' 
	WHEN 'PK' THEN 'PRIMARY KEY'
	WHEN 'S'  THEN 'System table'
	WHEN 'TR' THEN 'Trigger'
	WHEN 'UQ' THEN 'UNIQUE constraint'
	WHEN 'V'  THEN 'View' 
	WHEN 'X'  THEN 'Extended stored procedure'
	ELSE 'Unknown type' End
[Object Type],
crdate CreatedOn 
FROM SYSOBJECTS WHERE NAME like '%empl%'

Output