• Jagan Mohan Reddy Rajidi

SQL query to search a string in database Schema

Suppose to search an author name 'Steve' string in entire database schema then use following query.

Change the value of variable @Search according to your search.


DECLARE @Search VARCHAR(100)
set @Search='Steve'
SELECT o.name[Object Name],
CASE O.xtype  WHEN 'P' THEN 'Stored procedure'
			WHEN 'RF' THEN 'Replication filter stored procedure'
			WHEN 'TR' THEN 'Trigger'
			WHEN 'FN' THEN 'Scalar function'
			WHEN 'IF' THEN 'In-lined table-function' 
			WHEN 'IT' THEN 'Internal table'
			WHEN 'TF' THEN 'Table function' 
			WHEN 'V' THEN 'View' 
			WHEN 'X' THEN 'Extended stored procedure'
ELSE ' ' END[Object Type]
FROM syscomments c
INNER JOIN sysobjects o on o.id=c.id
WHERE text like '%' +@Search +'%'
ORDER BY [Object Name],[Object Type]



1 view0 comments

Recent Posts

See All

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 wi

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