Search String in Entire Database

Question

Post reply

Search String in Entire Database

tonyarp05 61903

SSCommitted

Points: 1515
More actions
November 19, 2012 at 10:48 pm

#284980

Hi,
I want to search a string in Entire Database. But it should not take more time to execute.
Someone please help me on this with script.
Regard,
Tony

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

BriPan SSCrazy Points: 2061 More actions · Answer 1

string means what is it any column value

like

select * from a where abc='xyz'

or any string in store procedure

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 2

Yes, Its column value. (any string from entire Database)

BriPan SSCrazy Points: 2061 More actions · Answer 3

any column string of entire database table.

can you tell me purpose of doing so

it may have batter alternate for the same.

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 4

The reason is, Suppose i want to search a string 'Family' in entire database. so that i could get the output which are tables(Fields) are having the value of 'Family'

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

a script can be used where you can dynamically pass the table and column names. but this will be time consuming task . as it needs to scan EVERY COLUMN of EVERY TABLE(data volumne will also plus the overhead).

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 6

tonyarp05 61903

SSCommitted

Points: 1515

November 19, 2012 at 11:52 pm

#1560619

Can you give the script how to search

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

tonyarp05 61903 (11/19/2012)
Can you give the script how to search

see this http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

vinu512 SSCoach Points: 15729 More actions · Answer 8

Try implementing the following procedure :

Create Procedure PR_SearchDatabase

@field NVarchar(400),

@input NVarchar(400)

As

Begin

Declare @tablename NVarchar(400), @sql NVarchar(Max)

Declare @table Table(Data NVarchar(max))

Declare TableName_Cursor Cursor LOCAL STATIC Forward_Only

For

Select Distinct a.name From sys.tables as a, sys.columns As b

Where a.name = OBJECT_NAME(b.object_id)

AND b.name = @field

Open TableName_Cursor

Fetch Next From TableName_Cursor Into @tablename

While(@@FETCH_STATUS = 0)

Begin

Set @sql = 'Select ' + @field + ' From ' + @tablename + ' Where ' + @field + ' LIKE ''%' + @input + '%'''

Insert Into @table

Execute(@sql)

Fetch Next From TableName_Cursor Into @tablename

End

Close TableName_Cursor

Deallocate TableName_Cursor

Select * From @table

End

Use it as a base and get a script or a procedure to work for your specific requirement.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Dont forget the NOLOCK too

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 10

this script is calling Field also, Without Field, the string need to search entire database(in all fields).

Please help on this

Ant-Green SSC Guru Points: 113536 More actions · Answer 11

http://www.sqlservercentral.com/scripts/String/89388/

It will take a lot of time and resources to do what you want to do, so I would strongly recommend going away and rethinking about what you need to do.

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 12

We are developing one tool(Backend : SQL server), that tool should be search the string from entire database.

But i have some codes,which takes min 30 to 45mins.

Even i tried with following string search also, its taking more than 30mins to search the string entire database.

http://www.sqlservercentral.com/scripts/String/89388/

Is there any other way we could get the result within few mins.. Please help me on this.

Ant-Green SSC Guru Points: 113536 More actions · Answer 13

No, as I have said what you are wanting to to is very costly in performance as it uses so many resources to look through each table and each column in X amount of rows to find matching rows where it contains a value of XYZ. Also it will only get worse with time and as the database grows, as I said, you really need to go away and reconsider what you are planning on this one.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

tonyarp05 61903 (11/20/2012)
We are developing one tool(Backend : SQL server), that tool should be search the string from entire database.
But i have some codes,which takes min 30 to 45mins.
Even i tried with following string search also, its taking more than 30mins to search the string entire database.
http://www.sqlservercentral.com/scripts/String/89388/
Is there any other way we could get the result within few mins.. Please help me on this.

i think you should start thinking to go with partitioning , so that you can divert the load to any designated disk (based on the keyword search)

here partitioning will be done on the basis of alphabets seggregation

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Search String in Entire Database

Cookies on SQLServerCentral