Metadata Queries: Listing Constraints on a Table
DB2
Query SYSCAT.TABCONST and SYSCAT.COLUMNS:
select a.tabname, a.constname, b.colname, a.type
from syscat.tabconst a, syscat.columns b
where a.table_name = 'table name'
and a.table_schema = 'schema'
and a.tabname = b.tabname
and a.tabschema = b.tabschema
Oracle
Query SYS.ALL_CONSTRAINTS and SYS.ALL_CONS_COLUMNS:
select a.table_name, a.constraint_name, b.column_name, a.constraint_type
from all_constraints a, all_cons_columns b
where a.table_name = 'table name'
and a.table_schema = 'schema'
and a.table_name = b.table_name
and a.owner = b.owner
and a.constraint_name = b.constraint_name
PostgreSQL, MySQL, and SQL Server
Query INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_ SCHEMA.KEY_COLUMN_USAGE:
select a.table_name, a.constraint_name, b.column_name, a.constraint_type
from information_schema.table_constraints a, information_schema.key_column_usage b
where a.table_name = 'table name'
and a.table_schema = 'schema'
and a.table_name = b.table_name
and a.table_schema = b.table_schema
and a.constraint_name = b.constraint_name
posted by kanun # 6:40 AM 
