Search This Blog

Total Pageviews

Monday, 9 August 2010

Oracle Col name search

col structure for a10
col "Data type" for a12

accept x_owner prompt "[= allowed ] Owner : ";
accept x_column prompt "[= allowed ] Column Name : ";

select owner,
table_name "Table name",
column_name "Column name",
data_type "Data type",
decode( upper( data_type ), 'NUMBER', to_char( data_precision ) || ',' || to_char( data_scale ),
'DATE', ' ',
'LONG', ' ',
to_char( data_length ) ) "Structure",
nullable "Nullable"
from dba_tab_columns
where ( ( substr( upper( nvl( '&&x_owner', 'x' ) ), 1, 1 ) <> '=' and owner like upper( '%&&x_owner%' ) )
or
( substr( upper( '&&x_owner' ), 1, 1 ) = '=' and owner = upper( substr( '&&x_owner', 2 ) ) )
)
and ( ( substr( upper( nvl( '&&x_column', 'x' ) ), 1, 1 ) <> '=' and column_name like upper( '%&&x_column%' ) )
or
( substr( upper( '&&x_column' ), 1, 1 ) = '=' and column_name = upper( substr( '&&x_column', 2 ) ) )
)
order by owner, table_name, column_name
/

No comments:

Oracle DBA

anuj blog Archive