lunes, 2 de noviembre de 2009

Script para ver los constraints de una tabla

Con el siguiente script en el Oracle SQL+ podrá listar los constraints de una tabla.

CONST.SQL
set echo off
set verify off
set linesize 320
set pagesize 2000
set numwidth 5
col constraint_type format a15
col column_name     format a40
col position format 999
break on constraint_name
clear buffer
accept ot prompt 'Ingrese SCHEMA.TABLE_NAME : '
ttitle left 'Constraints de la tabla '&ot
select a.constraint_name , 
       decode(a.constraint_type, 'C','Check', 'P','Primary', 
             'R','Foreign', 'U','Unique', 'Others') constraint_type, 
       b.column_name, b.position Pos 
from dba_constraints a, 
     dba_cons_columns b
where a.owner = b.owner
and   a.table_name = b.table_name
and   a.constraint_name = b.constraint_name
and   a.owner||'.'||a.table_name = trim(upper('&ot'))
order by 2,1
/
clear breaks
set numwidth 9
ttitle off
set verify on
set serverout on size 200000
set pagesize 2000
set linesize 80
Y luego muestra lo siguiente:

SQL> @CONST.SQL
Ingrese SCHEMA.TABLE_NAME : CONTAB.MONEY

Constraints de tabla CONTAB.MONEY
CONSTRAINT_NAME                CONSTRAINT_TYPE COLUMN_NAME                              POS
------------------------------ --------------- ---------------------------------------- ---
SYS_C00122003                  Check           MONEY_NAME
PK_JUEGOS                      Primary         MONEY_ID                                  1

2 rows selected.
Compartir:

1 comentario: