redgate SQL search

Question

redgate SQL search

george sibbald

SSC Guru

Points: 104210
More actions
November 18, 2013 at 7:00 am

#285354

can anyone here confirm that redgate SQL search will search for character strings in the DATA?
I have an as yet unknown number of strings to search for in a 200GB database and this feels like one of those situations where a tool would be useful.
cheers
george
---------------------------------------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

George i have the Redgate SQL Search plugin added, and it searches meta data only: object names from sys.objects, and definition text from sql_modules.

Sean Lange And I have a couple of posts on searching all columns for a value, take a look at this thread for exmaples:

http://www.sqlservercentral.com/Forums/Topic1246793-149-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

Lowell (11/18/2013)
George i have the Redgate SQL Search plugin added, and it searches meta data only: object names from sys.objects, and definition text from sql_modules.
Sean Lange And I have a couple of posts on searching all columns for a value, take a look at this thread for exmaples:
http://www.sqlservercentral.com/Forums/Topic1246793-149-1.aspx

ChrisM posted this gem awhile ago. It can be slightly modified to work for just about anything. It only generates the code to run but it should prove to be quite a bit faster than either of our old super slow cursor methods.

DECLARE @MySearchCriteria VARCHAR(500)

SET @MySearchCriteria = '''RO04381'',''RO04052'',''RO04210'''

SELECT 'SELECT ' + c.columnlist + ' FROM ' + t.name + ' WHERE ' + w.whereclause

FROM sys.tables t

CROSS APPLY (

SELECT STUFF((

SELECT ', ' + c.Name AS [text()]

FROM sys.columns c

WHERE t.object_id = c.object_id

AND c.collation_name IS NOT NULL

AND c.max_length > 6

FOR XML PATH('')

), 1, 2, '' )

) c (columnlist)

CROSS APPLY (

SELECT STUFF((

SELECT ' OR ' + c.Name + ' IN (' + @MySearchCriteria + ')' AS [text()]

FROM sys.columns c

WHERE t.object_id = c.object_id

AND c.collation_name IS NOT NULL

AND c.max_length > 6

FOR XML PATH('')

), 1, 4, '' )

) w (whereclause)

where c.columnlist + t.name + w.whereclause is not null --added by Sean Lange to eliminate NULLs

ORDER BY t.name

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

george sibbald SSC Guru Points: 104210 More actions · Answer 3

thanks guys, appreciate it.

I'll check that code out.

---------------------------------------------------------------------