"Turn" Informix data table structure analysis data collation constraint query code

Original address: http://blog.csdn.net/xqf222/article/details/6271219

This paper mainly collated the data of Informix database related system table, analyzed the whole Informix data table structure, and conveniently generated the code automatically.
Tip One: Systables store all data tables related data in the Informix database

Tip Two: Sysconstraints store all constraints related data in the Informix database

--Get the PRIMARY KEY constraint name for all user tables
Select a.tabname,b.constrname,b.* from Systables a joins sysconstraints B on A.tabid=b.tabid where A.tabid >99 and a. Tabtype= ' T ' and b.constrtype = ' P ' ORDER by A.tabname
--Get all user data tables without setting the primary key OK
SELECT * from Systables a where tabid >99 and tabtype= T ' and NOT EXISTS (select 1 from sysconstraints b where a.tabid= B.tabid and B.constrtype = ' P ');

--Gets the foreign KEY constraint name for all user tables
Select A.tabname,b.constrname from Systables a joins sysconstraints B on A.tabid=b.tabid where A.tabid >99 and A.tabt Ype= ' T ' and b.constrtype = ' R ' ORDER by A.tabname
---Get all user data tables that are not set foreign KEY constraints OK
SELECT * from Systables a where tabid >99 and tabtype= T ' and NOT EXISTS (select 1 from sysconstraints b where a.tabid= B.tabid and B.constrtype = ' R ');

--Get the CHECK constraint name for all user tables
Select A.tabname,b.constrname from Systables a joins sysconstraints B on A.tabid=b.tabid where A.tabid >99 and A.tabt Ype= ' T ' and b.constrtype = ' C ' ORDER by A.tabname
---Get all user data tables with no CHECK constraints set OK
SELECT * from Systables a where tabid >99 and tabtype= T ' and NOT EXISTS (select 1 from sysconstraints b where a.tabid= B.tabid and B.constrtype = ' C ');

--Gets the unique constraint name for all user tables
Select A.tabname,b.constrname from Systables a joins sysconstraints B on A.tabid=b.tabid where A.tabid >99 and A.tabt Ype= ' T ' and b.constrtype = ' U ' ORDER by A.tabname
--Get all user data tables with no unique constraints set OK
SELECT * from Systables a where tabid >99 and tabtype= T ' and NOT EXISTS (select 1 from sysconstraints b where a.tabid= B.tabid and B.constrtype = ' U ');

--Get the non-null constraint name for all user tables
Select A.tabname,b.constrname from Systables a joins sysconstraints B on A.tabid=b.tabid where A.tabid >99 and A.tabt Ype= ' T ' and b.constrtype = ' N ' ORDER by A.tabname
---Get all user data tables with non-null constraints set OK
SELECT * from Systables a where tabid >99 and tabtype= T ' and NOT EXISTS (select 1 from sysconstraints b where a.tabid= B.tabid and B.constrtype = ' N ');

"Turn" Informix data table structure analysis data collation constraint query code