Navi's

Necessity, the mother of invention

  • Visitor Map

    Locations of Site Visitors
  • Flag Counter

    free counters

Find any text in Sql server Database

Posted by Navi's on February 10, 2010


Hi,
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

-n’s

One Response to “Find any text in Sql server Database”

  1. Completely I share your opinion. It is good idea. It is ready to support you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: