SQL: Search Database Object Names

Do a simple search for all objects in the database with a specific text value.

copy

declare @search_text varchar(200)
 set @search_text = 'Users'
 
 SELECT  
			Objects.Name as ObjectName
			,Objects.type as ObjectType
			,Objects.type_desc as ObjectTypeDescription
			,Schemas.name AS SchemaName

FROM		sys.objects Objects 
			JOIN  sys.schemas Schemas ON Objects.schema_id = Schemas.schema_id

WHERE		Objects.name like '%' + @search_text + '%'

			-- optional filters
			-- and Objects.type_desc != 'SQL_STORED_PROCEDURE'
			-- and Objects.type not in ('PK', 'F')

ORDER BY  
			ObjectName
			,ObjectType
			,SchemaName