Application Scenario: if the field name is known, you can query all data tables in the database that contain the field name. Operation Method: Specify the field name and database table user, run the following query statement. The statement declaremycolumnnameVARCHAR (255): userid is generated in Oracle to query all data table records corresponding to the specified field name.
Application Scenario: if the field name is known, you can query all data tables in the database that contain the field name. Operation Method: Specify the field name and database table user, run the following query statement to query all data table records that contain the specified field name: declare mycolumnname VARCHAR (255): = 'userid'; -- Define to query
Application Scenario: if the field name is known, query all data tables in the database that contain the field name.
Operation Method: Specify the field name, database table user, and execute the following query statement.
-- Oracle generates query statements that contain records of all data tables corresponding to the specified field name
Declare
Mycolumnname VARCHAR (255): = 'userid'; -- defines the field name variable to be queried and changes it to the field name to be queried before running
Myownername VARCHAR (255): = 'system'; -- defines the database username variable to be queried, and changes it to the database username you want to query before running
Mystring NVARCHAR2 (4000): = ''; -- defines the string variable to be output
Cursor mycursor is -- defines the cursor
Select * from dba_tab_columns where lower (column_name) = mycolumnname and lower (owner) = myownername;
Myrecord mycursor % rowtype; -- defines the cursor record type
Counter int: = 0;
Begin
Open mycursor; -- open the cursor
If mycursor % isopen then -- determines whether the open is successful
Loop -- Obtain Record Sets cyclically
Fetch mycursor into myrecord; -- get records in the cursor
If mycursor % found then -- the cursor's found attribute determines whether a record exists
Mystring: = 'select * from' | myrecord. table_name | ';';
Dbms_output.put_line (mystring );
Else
Exit;
End if;
End loop;
Else
Dbms_output.put_line ('cursor not opened ');
End if;
Close mycursor;
End;
The running result is similar to 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;
