Find a particular text from all tables in DB.

295 pts.
Tags:
SQL Server
SQL Server database
Hi

There are 100 tables in my DB. I want to find if a text say "ABCD" is availbale in any these 100 tables. If it is availbale can we list those tables. Can someone help me out with the Query.

 

Regards Mayur

ASKED: January 6, 2010  11:46 AM
UPDATED: January 15, 2010  6:58 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

This gets a little involved, but if you break out the parts of this operation it isn’t too hard.

> create a table to hold the results:
tblnm = RESULTS
cols = TBL_OWNR, TBL_NAME, COL_NAME, CNT, LITERAL

> what we want to end up with is a list of queries, one qery for each column in each of your 100 tables. Something like this:
INSERT INTO RESULTS
‘owner of first table’ , ‘name of first table’ , ‘name of first column in first table’ ,
COUNT(*) , ‘ABCD’
FROM owner fo first table.name of first table
WHERE LOCATE (‘ABCD’,name of first column in first table’) <> 0;

That same statement must be repeated for each column in each of your hundred tables.

> use a query to create the insert statements.
something like this:
SELECT ‘INSERT INTO RESULTS’ ”table_owner” , ”table name” , ” column name” , ‘CO
UNT(*), ”ABCD” , FROM table_owner.tbl_name
‘WHERE LOCATE(”abcd”,column_name) <> 0′
FROM your rdbms table that contains the names of all tables and columns;

You will need to practice a bit on the SQL that writes other SQL. But once you have that ability, you will find all sorts of uses for it.

Good luck and good hunting.

[kccrosser] Some suggestions.
In the select to create the insert statements, only include columns that are of suitable data type and length (i.e., “CHAR”, “VARCHAR”, “NVARCHAR” where data_length >= 4).

I have done this before using dynamic SQL. In that approach, you open a cursor against the database table with the column definitions (filtering by type and length, of course), then for each table/column pair found, generate a sql statement to check the table – I usually use “select 1 from <table> where <column> like ‘%’+<search string>+’%’ “.
To be more efficient, open this as a second cursor and only do the initial fetch – if you get a hit, record the table/column pair, close the cursor and continue with the next table/column pair.

If you aren’t worried about different catalogs and schemas, you can get the user tables/columns in the current catalog/schema with:
select table_catalog, table_schema, table_name, column_name
from information_schema.columns
where character_maximum_length >= 4 — for your ABCD example

If you need to get it for all catalogs, then you need to repeat that query for each catalog, e.g.:

select table_catalog, table_schema, table_name, column_name
from <catalog>.information_schema.columns
where character_maximum_length >= 4 — for your ABCD example

If you have lots of catalogs, then this should really be done with dynamic SQL.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Mayurkhe
    SET NOCOUNT ON DECLARE @stringToReplace VARCHAR(100) DECLARE @schema sysname DECLARE @table sysname DECLARE @count INT DECLARE @sqlCommand VARCHAR(8000) DECLARE @where VARCHAR(8000) DECLARE @columnName sysname DECLARE @object_id INT CREATE TABLE #temp ( tablename VARCHAR(64), SearchValue VARCHAR(64) ) DECLARE @stringToFind VARCHAR(100) SET @stringToFind = 'ABCD' DECLARE TAB_CURSOR CURSOR FOR SELECT B.NAME AS SCHEMANAME, A.NAME AS TABLENAME, A.OBJECT_ID FROM sys.objects A INNER JOIN sys.schemas B ON A.SCHEMA_ID = B.SCHEMA_ID WHERE TYPE = 'U' ORDER BY 1 OPEN TAB_CURSOR FETCH NEXT FROM TAB_CURSOR INTO @schema, @table, @object_id WHILE @@FETCH_STATUS = 0 BEGIN DECLARE COL_CURSOR CURSOR FOR SELECT A.NAME FROM sys.columns A INNER JOIN sys.types B ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID WHERE OBJECT_ID = @object_id AND IS_COMPUTED = 0 AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext') OPEN COL_CURSOR FETCH NEXT FROM COL_CURSOR INTO @columnName WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlCommand = 'IF EXISTS (SELECT ' + @columnName + ' FROM ' + @schema + '.' + @table + ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'') ' SET @sqlCommand = @sqlCommand + 'INSERT INTO #temp Values (''' + @table + ''',''' + @stringToFind + ''')' --print cast(@sqlCommand as varchar(1000)) EXEC(@sqlCommand) FETCH NEXT FROM COL_CURSOR INTO @columnName END CLOSE COL_CURSOR DEALLOCATE COL_CURSOR FETCH NEXT FROM TAB_CURSOR INTO @schema, @table, @object_id END CLOSE TAB_CURSOR DEALLOCATE TAB_CURSOR SET NOCOUNT OFF Select * from #temp Drop table #temp
    295 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following