Find any text in Sql server Database
Posted by Navi's on February 10, 2010
Most of the time we faced a situation where we have to find a particular “text” in whole database.
So, this is not a big deal…
we can find any “text” in Database using below SQL..
Declare @text varchar(100) set @text='%your search string%' SELECT sys.sysobjects.*, sys.sysobjects.id, sys.syscomments.text, sys.sysobjects.xtype FROM sys.sysobjects INNER JOIN sys.syscomments ON sys.sysobjects.id = sys.syscomments.id WHERE sys.syscomments.text like @text
This will find “text” in all type of objects Stored procedure, functions, Views etc..
if you want to find the “text” in particular type of objects than you have to use [xtype=’P’ i.e. for Stored Procedure] in where clause like this
WHERE sys.syscomments.text like @text and xtype='P'
These are the type of objects codes which is used in sysobjects:
U – User table
D – Default constraint
P – Stored procedure
V – View
TR – Trigger
TF – Table Function (which returns as table)
FN – Function
either you can use above sql or create Stored Procedure.
Or just use this:
Search for SQL in your databases with Red Gate SQL Search