• Jagan Mohan Reddy Rajidi

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





55 views0 comments

Recent Posts

See All

Sometimes you get a scenario where you know the column name but don't remember which table it belongs to. Following query will search for a given column name in database schema and returns all objects