I have a value (like 'IT department'), based on this I want to get the table and column name in which this value exists/present.

  • You could use data dictionary views (like all_tables, all_tab_cols etc) and pl/sql to issue a series of queries searching for given value in all columns (of the right datatype) of all tables... but why would you want to do such a thing?
    – zgguy
    Commented Jun 5, 2015 at 11:53

3 Answers 3


Just run this in a database.

As long as its not a text / ntext field..

SET NOCOUNT ON  /* AUTHOR DT 10/19/15 Server: Microsoft SQL Server */

CREATE TABLE #TBL_SEARCH (server_name varchar(100), db_name varchar(100), table_name varchar(40), raw_tablename varchar(max), [schema_nm] varchar(max), matching_field varchar(40), counts int,  
A_SAMPLE varchar(100) 

DECLARE Table_Column_Matches CURSOR
    select '[' + OBJECT_SCHEMA_NAME(o.id) + '].[' + o.name + ']' as table_name, o.name as raw_table_name, 
    OBJECT_SCHEMA_NAME(o.id) as [schema_nm], c.name as field_name, t.name as datatype_field from sysobjects o
    join syscolumns c
    on o.id=c.id
    join systypes t
    on t.type=c.type and t.xtype=c.xtype and c.xusertype=t.xusertype
    o.type='U' and
    t.name IN ('char', 'nchar','nvarchar','varchar' )

DECLARE @table_name varchar(300)
DECLARE @field_name varchar(300)
DECLARE @raw_table_name varchar(300)
DECLARE @schema_nm varchar(300)
DECLARE @datatype_field varchar(300)
declare @query nvarchar(3000)

OPEN Table_Column_Matches

FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
WHILE (@@fetch_status <> -1)
    IF (@@fetch_status <> -2)
        set @query = 'SELECT @@SERVERNAME, DB_NAME(), ''' + @table_name + ''' as [table_name], ''[' + @raw_table_name + ']'' as raw_table_name,''[' + @schema_nm + ']'' as schema_nm, '''+ @field_name +''' as matching_field,
            COUNT([' + @field_name + ']) AS COUNTS, 
            MAX([' + @field_name + '] ) A_SAMPLE
            FROM  ' + @table_name + ' WITH (NOLOCK)
            WHERE UPPER([' + @field_name + ']) LIKE ''IT DEPT%''

        exec sp_executesql @query
    FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field

CLOSE Table_Column_Matches
DEALLOCATE Table_Column_Matches

select * from #TBL_SEARCH
where counts > 0
drop table #TBL_SEARCH 
  • You can either generate a lot of SQL (for each table). Maybe Thomas Kyte prepared some PL/SQL for this purpose.
  • or use brute force. If you have DBA and OS rights, you can "grep" Oracle datafiles. Then when you know datfile name, block size and offset where your string lies, you can idetify database block, database extent, database segment and you have it.

PS: it sounds like hard-core hacking, but if you know the offset, then simply execute statement alter system dump datafile abc block xyz'; and you will get all the information you need.

  • Why do you think that Thomas Kyte should deal with such problem?
    – miracle173
    Commented Jun 5, 2015 at 14:41
  • @miracle173 AFAIK I saw somewhere a PL/SQL script doing that, I'm not 100% sure who was the author. But such a requirement is quite common: stackoverflow.com/questions/208493/…
    – ibre5041
    Commented Jun 5, 2015 at 14:44
  • maybe we have an xy-problem
    – miracle173
    Commented Jun 6, 2015 at 9:59

You can use this one, however depending on your database size it may take a while to run. If possible add some AND TABLE_NAME IN ('TAB_1', 'TAB_2',...) in order to limit the amount of tables.

n number;
    for aTab in (select owner, table_name, column_name from all_tab_columns where data_type = 'VARCHAR2' order by table_name) loop
       execute immediate 'select COUNT(*) FROM '||aTab.owner||'.'||aTab.table_name||' WHERE '||aTab.column_name||' = :val'
       using 'IT Department' into n;
        if n > 0 then
          dbms_output.pu_line('Found in table '||aTab.owner||'.'||aTab.table_name||' column '||aTab.column_name);
        end if;
    end loop;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.