July 31, 2015 at 3:55 am
I need a script which will tell me Table name and column name where a given text is matching.
Example: search string = "I want to search something"
Output: Table T1 , Column C3.
DB: Microsoft SQL Server 2005 / 2008
July 31, 2015 at 7:34 am
spectra (7/31/2015)
I need a script which will tell me Table name and column name where a given text is matching.Example: search string = "I want to search something"
Output: Table T1 , Column C3.
DB: Microsoft SQL Server 2005 / 2008
I hope this is not something you plan on running all the time. The performance of this type of thing is horrendous to say the least. Effectively you are saying you want to search every column in every row of every table.
Here is a script that will generate the dynamic sql so that you can find a given value. This was originally posted by Chris M. I modified it a little bit so it handles so edge cases a little better.
DECLARE @MySearchCriteria VARCHAR(500)
SET @MySearchCriteria = '''I want to search something''' --you do need all these quotation marks because this string is injected to another string.
SELECT 'SELECT ' + c.columnlist + '] FROM [' + t.name + '] WHERE ' + w.whereclause as SelectStatement
FROM sys.tables t
CROSS APPLY (
SELECT STUFF((
SELECT '], [' + c.Name AS [text()]
FROM sys.columns c
join sys.types t2 on t2.user_type_id = c.user_type_id
WHERE t.object_id = c.object_id
AND c.collation_name IS NOT NULL
AND c.max_length > 6
and t2.name not in ('text', 'ntext')
FOR XML PATH('')
), 1, 2, '' )
) c (columnlist)
CROSS APPLY (
SELECT STUFF((
SELECT ' OR [' + c.Name + '] IN (' + @MySearchCriteria + ')' AS [text()]
FROM sys.columns c
join sys.types t2 on t2.user_type_id = c.user_type_id
WHERE t.object_id = c.object_id
AND c.collation_name IS NOT NULL
AND c.max_length > 6
and t2.name not in ('text', 'ntext')
FOR XML PATH('')
), 1, 4, '' )
) w (whereclause)
where c.columnlist is not null
ORDER BY t.name
There are some other examples around this site. I know there are a couple of very old cursor versions, one written by me and the other one by Lowell. The important thing here is that this is horribly inefficient and is a great analysis tool but but production this is simply awful.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply