|
|
| Re: Need to know how to query from Data Dict. to find out table name and col. name wich contain the [message #60423 is a reply to message #60358] |
Fri, 06 February 2004 08:57   |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
OK ...I hope at least you have the login/password for this...
1. Login as user
2. hint: use table 'user_tab_columns'
3. Since you are to find a 'string', you can avoid the number and date fields.
4. Write a Pl/SQL script to go through ALL the table and individual columns in a loop
select count(1) from table tab_a where col_a like '%string%';
select count(1) from table tab_a where col_b like '%string%';
select count(1) from table tab_b where col_a like '%string%';
....
5. check the spool file to see which one of them satisfy the condition.
Good luck.
--
Sanjay
|
|
|
|
| Re: Need to know how to query from Data Dict. to find out table name and col. name wich contain the [message #60460 is a reply to message #60423] |
Mon, 09 February 2004 07:28 |
Sunil
Messages: 132
Registered: September 1999
|
Senior Member |
|
|
Hi,
Find below a script that will print all the tables in a database and all the columns in different tables. It contains a dynamic SQL query that finds the name of columns in all the available tables. you can include one more dynamic sql to search all tables, with all columns , that are returned by these 2 queries.
Hope its useful.
Regards
Sunil
DECLARE
CURSOR LIST_OF_TABLES IS SELECT * FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE';
ROW_DATA LIST_OF_TABLES%ROWTYPE;
TABLENAME VARCHAR2(30) ;
STMT VARCHAR2(200);
CUR_HDL INT;
ROWS_PROCESSED INT;
NAME_OF_THE_COLUMN VARCHAR2(30);
NAME_OF_THE_TABLE VARCHAR2(30);
BEGIN
FOR ROW_DATA in LIST_OF_TABLES LOOP
dbms_output.enable(500000);
SELECT ROW_DATA.SEGMENT_NAME INTO TABLENAME FROM DUAL;
CUR_HDL := dbms_sql.open_cursor;
STMT:= ’SELECT TABLE_NAME, COLUMN_NAME FROM COLS WHERE TABLE_NAME = :TABLENAME';
dbms_sql.parse(CUR_HDL , STMT, dbms_sql.native);
dbms_sql.bind_variable(cur_hdl, ’TABLENAME’, TABLENAME);
rows_processed := dbms_sql.execute(cur_hdl);
dbms_sql.define_column(cur_hdl, 1, NAME_OF_THE_TABLE, 200);
dbms_sql.define_column(cur_hdl, 2, NAME_OF_THE_COLUMN,30);
DBMS_OUTPUT.PUT_LINE('--------------------------------' );
DBMS_OUTPUT.PUT_LINE(TABLENAME );
DBMS_OUTPUT.PUT_LINE('--------------------------------' );
LOOP
IF dbms_sql.fetch_rows(cur_hdl) > 0 then
dbms_sql.column_value(cur_hdl, 1 , NAME_OF_THE_TABLE );
dbms_sql.column_value(cur_hdl, 2, NAME_OF_THE_COLUMN);
DBMS_OUTPUT.PUT_LINE(NAME_OF_THE_COLUMN);
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(cur_hdl);
END LOOP;
END;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
