Eliminar todas las tablas de una base de datos PostgreSQL

Procedimiento:

1- Utilizamos el siguiente comando para generar automáticamente un script SQL con todos los DROPs:

~$ psql -h <hostname> -t -d <database> -U <username> -c "SELECT
'DROP TABLE ' || n.nspname || '.' || c.relname || ' CASCADE;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;" > droptables

Donde:

• <hostname> = Nombre de la máquina donde reside la base de datos
• <database> = Nombre de la base de datos
• <username> = Nombre de usuario

2- Ejecutamos el script generado en el paso anterior:

~$ psql -h <hostname> -d <database> -U <username> -f droptables

Consultar todos los índices en PostgreSQL y Oracle

PostgreSQL (hasta 14 índices por tabla):

SELECT
UPPER(t.relname) as table_name
UPPER(a.attname) as column_name
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a,
pg_namespace
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND (
a.attnum = ix.indkey[0]
OR a.attnum = ix.indkey[1]
OR a.attnum = ix.indkey[2]
OR a.attnum = ix.indkey[3]
OR a.attnum = ix.indkey[5]
OR a.attnum = ix.indkey[6]
OR a.attnum = ix.indkey[7]
OR a.attnum = ix.indkey[8]
OR a.attnum = ix.indkey[9]
OR a.attnum = ix.indkey[10]
OR a.attnum = ix.indkey[11]
OR a.attnum = ix.indkey[12]
OR a.attnum = ix.indkey[13]
OR a.attnum = ix.indkey[14]
)
AND t.relkind = 'r'
AND t.relnamespace = pg_namespace.oid
AND pg_namespace.nspname = 'public'
ORDER BY
t.relname,
a.attname;

Oracle:

SELECT table_name, column_name
FROM user_ind_columns
ORDER BY table_name, column_name