En este artículo vamos a ver como acceder a las tablas del sistema de Oracle, para poder obtener información sobre las bases de datos, por ejemplo las tablas de una base de datos, los campos de los que consta, los procedimientos almacenados, las claves primarias, etc.
Esta información nos puede ser bastante útil por ejemplo para documentar la base de datos de forma automática, para crear los procedimientos almacenados automáticamente en función de los campos de las tablas o cualquier otra utilidad que se nos ocurra.
En el trabajo me he creado un generador de procedimientos almacenados que crea los PA de inserción, modificación, borrado y consulta de cualquier tabla de la base de datos, el cual nos está ahorrando mucho esfuerzo y horas de desarrollo a todo el equipo. La base de datos sobre la que estamos desarrollando la cambian continuamente, por lo que hay que rehacer los procedimientos almacenados cada dos por tres y gracias a está herramienta es automática la actualización de los PA.
A continuación paso a especificar algunas de las consultas a las tablas del sistema que pueden resultar de mayor utilidad:
La consulta que nos devuelve todas las tablas es la siguiente:
SELECT TABLE_NAME FROM USER_TABLES
Para obtener información sobre los campos o columnas de la tabla “NOMBRE_DE_LA_TABLA”, incluyendo el nombre del campo, el tipo de datos (incluyendo precisión y escala si procede), si admite nulos y los comentarios que tenga asociados utilizaremos la siguiente consulta:
SELECT COLS.*, COM.COMMENTS
FROM SYS.ALL_TAB_COLUMNS COLS
LEFT JOIN SYS.ALL_COL_COMMENTS COM ON COLS.TABLE_NAME = COM.TABLE_NAME AND COLS.COLUMN_NAME = COM.COLUMN_NAME
WHERE COLS.OWNER = USER AND COM.OWNER = USER AND COLS.TABLE_NAME = 'NOMBRE_DE_LA_TABLA'
Si queremos obtener los campos que forman la clave principal o primary key (PK) de la tabla “NOMBRE_DE_LA_TABLA” usaremos:
SELECT CONS.TABLE_NAME, COLS.COLUMN_NAME, COMMENTS
FROM SYS.ALL_CONSTRAINTS CONS, SYS.ALL_IND_COLUMNS COLS
LEFT JOIN SYS.ALL_COL_COMMENTS COM ON COLS.TABLE_NAME = COM.TABLE_NAME AND COLS.COLUMN_NAME = COM.COLUMN_NAME
WHERE COLS.INDEX_OWNER = USER AND CONS.OWNER = USER AND COM.OWNER = USER AND CONS.CONSTRAINT_TYPE = 'P' AND CONS.CONSTRAINT_NAME = COLS.INDEX_NAME AND CONS.TABLE_NAME ='NOMBRE_DE_LA_TABLA'
Para obtener la información de todos los triggers de la base de datos, incluyendo su nombre, código fuente del trigger, el evento que lo dispara, la tabla a la que esta asociado, etc:
SELECT * FROM SYS.ALL_TRIGGERS WHERE OWNER = USER
La siguiente consulta nos devolverá todas las claves foráneas (FK’s) de la tabla “NOMBRE_DE_LA_TABLA”
SELECT * FROM SYS.ALL_CONSTRAINTS
WHERE OWNER = USER AND TABLE_NAME ='NOMBRE_DE_LA_TABLA' AND CONSTRAINT_TYPE = 'R'
Por último la siguiente solicitud a la base de datos nos devolverá el código fuente de todos los paquetes de la base de datos, incluyendo tanto la cabecera como el cuerpo. Se devuelve un registro por cada línea del paquete.
SELECT * FROM SYS.ALL_SOURCE
WHERE OWNER = USER AND INSTR(SYS.ALL_SOURCE.TYPE,'PACKAGE') > 0
ORDER BY NAME, TYPE, LINE
Es importante destacar que en las consultas anteriores al filtrar por una determinada tabla su nombre debe ir en mayúsculas.
El filtro de OWNER = USER lo establecemos para obtener únicamente la información de los objetos de los que somos propietarios.
