May 13, 2011 at 7:33 am
Hi all,
A collegue asked me today if there was a fast way to search for a particular sting is a database table not knowing the database or the table.
As you can imagine this could be a daunting task if you had to do it manually!
For example they needed to find the word "Consumption" and they didn't know what database, table or column that the word was in.
Any way I threw a query together that uses a Database cursor to get all of tables and columns into a table and then another cursor to go through these results to search through each column with the result set returning the Database, Schema, Table, Column and the Total Count.
It worked a treat!
It is by no means polished but it does the job!
To use all you have to do is change the @Search_Text parameter - it is important to keep the % if you want to search within text.
Hayden
DECLARE @sql VARCHAR(MAX)
DECLARE @sql_db VARCHAR(MAX)
DECLARE @Search_Text VARCHAR(MAX) = '%Consumption%'
DECLARE @tbl_COLUMNS TABLE
(
id INT IDENTITY(1,1)
, TABLE_CATALOG VARCHAR(MAX)
, TABLE_SCHEMA VARCHAR(MAX)
, TABLE_NAME VARCHAR(MAX)
, COLUMN_NAME VARCHAR(MAX)
)
DECLARE @tbl_RESULTS TABLE
(
TABLE_CATALOG VARCHAR(MAX)
, TABLE_SCHEMA VARCHAR(MAX)
, TABLE_NAME VARCHAR(MAX)
, COLUMN_NAME VARCHAR(MAX)
)
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN('Tempdb', 'Master') ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @sql_db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM [' + @sql_db + '].INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE LIKE''' + '%CHAR%' +''' ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME'
INSERT INTO @tbl_COLUMNS (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
EXEC(@sql)
FETCH NEXT FROM db_cursor INTO @sql_db
END
CLOSE db_cursor
DEALLOCATE db_cursor
DECLARE @sql_tbl_id INT
DECLARE @db VARCHAR(MAX)
DECLARE @sc VARCHAR(MAX)
DECLARE @tb VARCHAR(MAX)
DECLARE @cl VARCHAR(MAX)
DECLARE column_cursor CURSOR FOR
SELECT id FROM @tbl_COLUMNS ORDER BY id
OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @sql_tbl_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@db = TABLE_CATALOG
, @sc = TABLE_SCHEMA
, @tb = TABLE_NAME
, @cl = COLUMN_NAME
FROM @tbl_COLUMNS
WHERE id = @sql_tbl_id
SET @sql = 'SELECT ''' + @db + ''' AS TABLE_CATALOG, ''' + @sc + ''' AS TABLE_SCHEMA, ''' + @tb + ''' AS TABLE_NAME, ''' + @cl + ''' AS COLUMN_NAME FROM [' + @db + '].[' + @sc + '].[' + @tb + '] WHERE ' + @cl + ' LIKE ''' + @Search_Text + ''''
INSERT INTO @tbl_RESULTS (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
EXEC(@sql)
FETCH NEXT FROM column_cursor INTO @sql_tbl_id
END
CLOSE column_cursor
DEALLOCATE column_cursor
SELECT
TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, COUNT(*) AS TOTAL_COUNT
FROM @tbl_RESULTS
GROUP BY
TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
May 13, 2011 at 7:41 am
maybe use full text searching?
May 13, 2011 at 7:46 am
arun_s (5/13/2011)
maybe use full text searching, then use FREETEXT command to search for words containing Specific character values
So on top of search everywhere you now have to setup fts on all tables in all db??? Not sure this solution scale well!
May 13, 2011 at 10:00 am
Is this a one time thing??? I created a script several years ago to do just this sort of thing but it is 100% **cough** cursor **cough** based. It was slower than molasses in Alaska but it did work. I can see if I can find that nasty old thing.
_______________________________________________________________
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/
May 13, 2011 at 10:03 am
Sean Lange (5/13/2011)
Is this a one time thing??? I created a script several years ago to do just this sort of thing but it is 100% **cough** cursor **cough** based. It was slower than molasses in Alaska but it did work. I can see if I can find that nasty old thing.
Sometimes you got to do what you got to do! 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply