Application: Known field name, querying all data tables in the database that contain the field name
Action: Specify the field name, database table user, execute the following query statement can
--oracle Build query contains all data table record statements that correspond to the specified field names
Declare
Mycolumnname VARCHAR (255): = ' userid ';--Define the field name variable to query, modify it to the name of the field you want to query before running
Myownername VARCHAR (255): = ' system ';--Define the database user name variable to be queried and modify it to the database user name you want to query before running
MyString NVARCHAR2 (4000): = "; --Define the string variables to output
Cursor MyCursor is--defines a cursor
SELECT * from Dba_tab_columns where lower (column_name) =mycolumnname and Lower (owner) =myownername;
Myrecord Mycursor%rowtype; --Define Cursor record type
Counter int: = 0;
Begin
Open mycursor; --Open cursor
If Mycursor%isopen then--Judging open success
Loop-Loop Get recordset
Fetch mycursor into Myrecord; --Get the record in the cursor
If Mycursor%found then--the found property of the cursor determines if there is a record
Mystring:= ' select * from ' | | myrecord.table_name| | '; ';
Dbms_output.put_line (mystring);
Else
Exit
End If;
End Loop;
Else
Dbms_output.put_line (' Cursor not open ');
End If;
Close MyCursor;
End
The run result resembles the following statement:
SELECT * from Def$_propagator;
SELECT * from Repcat$_repcatlog;
SELECT * from Repcat$_repgroup_privs;
SELECT * from Sqlplus_product_profile;
SELECT * from Product_privs;
