September 13, 2013 at 8:22 am
I want to find a value anywhere in a SQL Server Database. what is the solution ?
search String = "3496" // exact data search
It would search all table , all column for varchar , number fields etc ....finally output the name of the table and column where this match is found.
Is there any workaround ?
September 13, 2013 at 8:38 am
ChrisM posted an awesome script the other day. I modified it slightly for you case.
DECLARE @MySearchCriteria VARCHAR(500)
SET @MySearchCriteria = '3496'
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 + ' = (' + @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)
ORDER BY t.name
This should get you started. I certainly hope you don't plan on running this type of query on a regular basis. 😛
_______________________________________________________________
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/
September 13, 2013 at 1:52 pm
Some points to consider:
Be sure to make the search value a character string, viz:
SET @MySearchCriteria = '''3496'''
"Max_length" is bytes, which works fine for (n)(var)char, but not so much for int, etc..
Presumably you want to exclude dates, datetimes and certain other specific data types from your search.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 13, 2013 at 8:21 pm
I run this code but it did not tell me the name of the table and column where match is found.
this is not giving correct output.
September 14, 2013 at 2:16 am
Never mind....this is resolved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply