How display list of foreign key constraints, including WITH [NO] CHECK OPTION setting?

Database

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!

3 REPLIES
Senior Apprentice

Re: How display list of foreign key constraints, including WITH [NO] CHECK OPTION setting?

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

Enthusiast

Re: How display list of foreign key constraints, including WITH [NO] CHECK OPTION setting?

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.

Re: How display list of foreign key constraints, including WITH [NO] CHECK OPTION setting?

Thanks for the feedback!