November 19, 2012 at 10:48 pm
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
November 19, 2012 at 11:13 pm
string means what is it any column value
like
select * from a where abc='xyz'
or any string in store procedure
November 19, 2012 at 11:19 pm
Yes, Its column value. (any string from entire Database)
November 19, 2012 at 11:31 pm
any column string of entire database table.
can you tell me purpose of doing so
it may have batter alternate for the same.
November 19, 2012 at 11:36 pm
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'
November 19, 2012 at 11:43 pm
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;-)
November 19, 2012 at 11:52 pm
Can you give the script how to search
November 20, 2012 at 12:20 am
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;-)
November 20, 2012 at 12:39 am
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.
November 20, 2012 at 12:43 am
Dont forget the NOLOCK too 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 20, 2012 at 1:45 am
this script is calling Field also, Without Field, the string need to search entire database(in all fields).
Please help on this
November 20, 2012 at 1:48 am
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.
November 20, 2012 at 4:20 am
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.
November 20, 2012 at 4:25 am
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.
November 20, 2012 at 4:34 am
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;-)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply