0

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 Jun 5 '15 at 11:53
1

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
READ_ONLY
FOR (
    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
    where 
    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)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        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%''
        '

        INSERT INTO #TBL_SEARCH
        exec sp_executesql @query
    END
    FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
END

CLOSE Table_Column_Matches
DEALLOCATE Table_Column_Matches

select * from #TBL_SEARCH
where counts > 0
drop table #TBL_SEARCH 
0
  • 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.

0

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.

declare
n number;
begin
    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;
end;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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