1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
|
-- Le SELECT
SQL> select 'SELECT count(*), ''' || T.table_name || ''', ''' || T.column_name || '''' || ' FROM ' || O.object_name || ' WHERE upper(' || T.column_name || ') = ''TEXAS'';' FROM dba_objects O, dba_tab_cols T where T.table_name = O.object_name and O.object_type = 'TABLE' and T.owner = 'HR' and T.data_type = 'VARCHAR2' order by T.table_name, T.column_name ;
-- Contenu du fichier .sql
'SELECTCOUNT(*),'''||T.TABLE_NAME||''','''||T.COLUMN_NAME||''''||'FROM'||O.OBJECT_NAME||'WHEREUPPER('||T.COLUMN_NAME||')=''TEXAS'';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS';
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS';
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS';
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS';
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS';
30 rows selected. |
Partager