SQL Scripts‎ > ‎A-I‎ > ‎

desc

set echo off
set verify off
set pages 100
-----------------------------------------------------------------
-- @name: desc
-- @author: dion cho
-- @note: extended describe
-- @usage: @desc schema_name object_name
-- @example: @desc user t1
-----------------------------------------------------------------

define __SCH_NAME = &1
define __OBJ_NAME = &2

col object_type new_value object_type

select object_type
from dba_objects
where owner = upper('&__SCH_NAME') and object_name = upper('&__OBJ_NAME')
;

desc &__SCH_NAME..&__OBJ_NAME


-- for table
set serveroutput on
begin
    if '&object_type' = 'TABLE' then
      dbms_output.put(rpad('Index',20));
      for cdx in (select c.column_id, c.column_name from dba_tab_columns c where
              c.owner = upper('&__SCH_NAME') and c.table_name = upper('&__OBJ_NAME')
                and exists (select 1 from dba_ind_columns i
                      where i.table_name = upper('&__OBJ_NAME')
                            and i.table_owner = upper('&__SCH_NAME')
                            and column_name = c.column_name)
                  order by c.column_id asc
                      ) loop
        dbms_output.put(rpad(cdx.column_id||'',4,'.'));
      end loop;
      dbms_output.new_line;
      for idx in (select owner, uniqueness, index_name, table_name from dba_indexes
            where owner = upper('&__SCH_NAME') and
                  table_name = upper('&__OBJ_NAME')) loop
          dbms_output.put(rpad(substr(idx.index_name,1,20), 20,'.'));
        for cdx in (select c.column_id, c.column_name
                from dba_tab_columns c
                where c.owner = idx.owner and c.table_name = idx.table_name
                      and exists (select 1 from dba_ind_columns i
                      where i.table_name = upper('&__OBJ_NAME')
                            and i.table_owner = upper('&__SCH_NAME')
                            and i.column_name = c.column_name)
                    order by c.column_id asc) loop
          for icx in (select min(column_position)||'' as cpos
                from dba_ind_columns
                where index_owner = idx.owner and index_name = idx.index_name
                      and column_name = cdx.column_name) loop
              if icx.cpos is null then
                dbms_output.put(rpad(' ', 4));
              else
                if idx.uniqueness = 'UNIQUE' then
                  dbms_output.put(rpad(icx.cpos||'U',4));
                else
                  dbms_output.put(rpad(icx.cpos||'',4));
                end if;
              end if;
          end loop;
        end loop;
        dbms_output.new_line;
      end loop;
    end if;
end;
/
set serveroutput off

-- create ddl
set long 100000
select dbms_metadata.get_ddl(d.object_type, d.object_name, d.owner) as ddl
from dba_objects d
where object_name = upper('&__OBJ_NAME')
      and owner = upper('&__SCH_NAME')
;

-- create ddl for indexes for table
select dbms_metadata.get_ddl(d.object_type, d.object_name, d.owner) as ddl
from dba_objects d
where '&object_type' = 'TABLE'
            and owner = upper('&__SCH_NAME')
            and object_name in (select index_name from dba_indexes
                            where owner = d.owner and table_name = upper('&__OBJ_NAME'))
;

set echo on
set verify on

Comments