Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Search String in Entire Database Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 10:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:50 PM
Points: 60, Visits: 252
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
Post #1386684
Posted Monday, November 19, 2012 11:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
string means what is it any column value
like
select * from a where abc='xyz'

or any string in store procedure
Post #1386687
Posted Monday, November 19, 2012 11:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:50 PM
Points: 60, Visits: 252
Yes, Its column value. (any string from entire Database)
Post #1386688
Posted Monday, November 19, 2012 11:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
any column string of entire database table.

can you tell me purpose of doing so
it may have batter alternate for the same.

Post #1386691
Posted Monday, November 19, 2012 11:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:50 PM
Points: 60, Visits: 252
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'

Post #1386692
Posted Monday, November 19, 2012 11:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1386695
Posted Monday, November 19, 2012 11:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:50 PM
Points: 60, Visits: 252
Can you give the script how to search
Post #1386697
Posted Tuesday, November 20, 2012 12:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1386705
Posted Tuesday, November 20, 2012 12:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 1,118, Visits: 1,582
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
Post #1386708
Posted Tuesday, November 20, 2012 12:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
Dont forget the NOLOCK too

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1386710
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse