>e.g. I want to see all the occurences of '#' in all the columns in all
>the tables in the database. Is ther a way to write this w/o having to
>hardcode each table and column in the DB?
Sure, using dynamic SQL. As this implies using PL/SQL, the only way to send
the table names to the output is using dbms_output. This can give you
problems if too much output is generated. The following script originally
checked for Y2k problems, and I have deleted some parts. It has not been
tested. Table name, column name and result are stored in a table
column_checks for later reference:
Success,
Arjan.
- table might alredy exist:
whenever sqlerror continue
--
create table column_checks
( check_date date not null
, table_name varchar2(132)
, column_name varchar2(132)
, check_result varchar2(50)
);
--
whenever sqlerror exit failure
--
- Delete uncompleted checks in case of restart of this script:
--
delete from column_checks
where check_result = 'Checking';
commit;
--
declare
l_result integer;
--
cursor c_Cols is
select usercol.table_name, usercol.column_name
from user_tab_columns usercol
where usercol.data_type in ( 'VARCHAR2', 'CHAR' )
and usercol.table_name != 'COLUMN_CHECKS'
and not exists
( select null
from column_checks checked
where checked.table_name = usercol.table_name
and checked.column_name = usercol.column_name
and checked.check_result != 'Checking'
);
--
- Dynamic SQL function to execute any query which returns
- an integer value:
--
function ExecQuery( p_query in varchar2)
return integer
is
l_cursorid integer;
l_rowcount integer;
l_result integer;
begin
l_result := null;
--
l_cursorid := dbms_sql.open_cursor;
dbms_sql.parse( l_cursorid, p_query, dbms_sql.native);
dbms_sql.define_column( l_cursorid, 1, l_result);
l_rowcount := dbms_sql.execute_and_fetch( l_cursorid, true);
dbms_sql.column_value( l_cursorid, 1, l_result);
dbms_sql.close_cursor( l_cursorid);
--
if l_result is null then
dbms_output.put_line( 'No result for ' || p_query);
end if;
--
return l_result;
exception
when others then
if dbms_sql.is_open( l_cursorid) then
dbms_sql.close_cursor( l_cursorid);
end if;
dbms_output.put_line( 'ExecQuery: error executing ' || p_query);
raise;
end ExecQuery;
--
begin
for r_Col in c_Cols loop
--
insert into column_checks
( check_date, table_name, column_name
, check_result
)
values
( sysdate, r_Col.table_name, r_Col.column_name
, 'Checking'
);
commit;
--
l_result := ExecQuery
( 'select count(*)'
|| ' from ' || r_Col.table_name
|| ' where ' || r_Col.column_name || ' like ''%#%'' '
);
--
if l_result != 0 then
update column_checks
set check_result = 'Rows: ' || to_char( l_result)
where table_name = r_Col.table_name
and column_name = r_Col.column_name;
commit;
else
update column_checks
set check_result = 'None'
where table_name = r_Col.table_name
and column_name = r_Col.column_name;
commit;
end if;
end loop;
end;
/
--
- Show the results. Results will be kept in table column_checks
- for later use:
--
set pagesize 60
column check_result format a10 heading 'Result'
column table_name format a37 heading 'Table'
column column_name format a29 heading 'Column'
select table_name, column_name, check_result
from column_checks
where check_result = 'None';
select table_name, column_name, check_result
from column_checks
where check_result != 'None';
select check_result, count(*)
from column_checks
group by check_result;
Received on Sun Aug 23 1998 - 08:19:52 CDT
