June 1, 2015 at 3:10 am
Hi all,
I have a task is to find which columns in which tables in which databases content the value that needed to find. For example I need to find the value like '%a123%' where it is allocated? in which columns or which tables or which databases.
Thanks in advance for your ideas,
Minh
June 1, 2015 at 3:16 am
You'll need to query every single table and search on every single column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2015 at 3:18 am
Products such as RedGate SQL Search[/url] and Apex SQL Search seem to be the most reasonable way to skin this cat.
If you have to do it with T-SQL, a quick Google search for "search all columns in all tables for a string" returns millions of matches. Does any of those help you?
-- Gianluca Sartori
June 1, 2015 at 9:27 pm
Thanks Gianluca!
June 4, 2015 at 4:08 pm
By default, SQL Server maintains histogram type statistics on columns referenced in conditional expressions. When supplied with table and column name, the DBCC SHOW_STATISTICS command will return a grouping of RANGE_HI_KEY values, if the column has statistics built on it. Even if it doesn't contain the exact key value you're looking for (and it probably won't), it will give you a clue about what type of codes are contained in that column.
For example:
RANGE_HI_KEY
-----------------
Florida
Arizona
...
What the following t-sql ditty will do is dynamically construct into the @sql variable a DBCC SHOW_STATISTICS call for every CHAR based column in the current database, and then it will EXECUTE the commads.
It filters where CHARACTER_MAXIMUM_LENGTH >= 10 to limit the number of columns scanned, and you can adjust this as needed depending on the length of your search key, but really this is so fast it will scan across hundreds of columns within a few seconds, so can leave that filter off entirely.
Trust me; it's harmless. 🙂
It might help you locate what you're looking for without performing an actual page scan across every table in the database.
declare @sql varchar(max) = '';
SELECT @sql = @sql
+'PRINT '''+TABLE_SCHEMA+'.'+TABLE_NAME+'.'+COLUMN_NAME
+'''; DBCC SHOW_STATISTICS ('''
+TABLE_SCHEMA+'.'+TABLE_NAME+''','''+COLUMN_NAME
+''') WITH NO_INFOMSGS, HISTOGRAM'+CHAR(13)+CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%CHAR%' AND CHARACTER_MAXIMUM_LENGTH >= 10;
EXEC( @sql );
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply