From: | MARIA ANTONIETA RAMIREZ SOLIS <maramirez(at)ulsaneza(dot)edu(dot)mx> |
---|---|
To: | pgsql-es-ayuda(at)postgresql(dot)org |
Subject: | Consulta para saber las llaves primarias y foraneas de una base de datos |
Date: | 2015-05-13 17:17:45 |
Message-ID: | CALGh7Et4qFozT0YTptOujJUZ4PxOxCyA-ui_PoddWa36x6y=Ew@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
Buen dia
Les comparto una consulta que me dio lo que necesitaba , que era saber las
llaves foraneas y primarias de todas las tablas en un solo query.
SELECT tc.table_name,
tc.constraint_name,
tc.constraint_type,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE lower(tc.constraint_type) in ('foreign key', 'primary key')
ORDER BY tc.table_name
Saludos!!
From | Date | Subject | |
---|---|---|---|
Next Message | raul andrez gutierrez alejo | 2015-05-13 22:20:24 | Re: QUERY PARA SABER LAS COLUMNAS DE LAS TABLAS Y SUS LLAVES PRIMARIAS |
Previous Message | Guillermo E. Villanueva | 2015-05-13 12:08:54 | Re: Ayuda para optimizar consulta |