All Forums Database
billhoover 2 posts Joined 05/14
23 May 2014
How display list of foreign key constraints, including WITH [NO] CHECK OPTION setting?

Selecting from DBC.DISTINCT_RI_PARENTS gives me a list of all foreign key constraints in the database.  But it doesn't show me which of the constraints have WITH NO CHECK OPTION specified and which do not.  Currently, I'm using SQL Assistant to manually do Show Definition for each table (one by one) to see which FK constraints have WITH NO CHECK OPTION (some do, some don't).  Instead, I'd like to run a single query to show me all this, if possible.  Thanks!

dnoeth 4628 posts Joined 11/04
25 May 2014

There's no way to get that info from the dbc tables/views, this is only stored in the table header :-(


Raja_KT 1246 posts Joined 07/09
26 May 2014

Hi Bill,

If the work is repititive and huge number of tables, then probably an automation unix script is required. Export(bteq or fastexport) ddl or sql to a file. Then in the same script run unix for/while/do while loop; use egrep or awk to find "WITH NO CHECK OPTION" or "WITH CHECK OPTION", redirecting  the output to files for no check option or check option.


I feel that for every feature not available , work arounds are there in Teradata, because of the availability of its

numerous utilities, tools (and interfaces too) and run on the best Operating systems where we can develop/customize/enhance TD programs.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

billhoover 2 posts Joined 05/14
30 May 2014

Thanks for the feedback!

You must sign in to leave a comment.