Is there a column I can tap into to figure out if a table is a multiset. RequestText in DBC.Tables is not a good column as it has the latest requesttext, so if the table was altered it has that DDL and not the CREATE TABLE statement? The CheckOpt column also does not seem to be a good indication of a MultiSet table. We are on the 14.10.03.03 Release.
There was another forum thread about this topic a year ago:
The Teradata Database Data Dictionary Reference book documents the DBC.TablesV.CheckOpt column as indicating whether or not the table allows duplicate rows: Y = Duplicate rows are allowed, N = Duplicate rows are not allowed.
As the old forum thread noted, if CheckOpt contains a Y for the table, then you are guaranteed that the table is a MULTISET table. But if CheckOpt contains an N, it's less clear.
SET table --> CheckOpt = "N"
MULTISET table with no constraint --> CheckOpt = "Y"
MULTISET table with unique constraint added --> CheckOpt = "N"
MULTISET table after constraint dropped --> CheckOpt = "Y"
I don't believe there is a column in the Data Dictionary that directly indicates whether a table is SET versus MULTISET.
A possible workaround would be to use the SHOW IN XML TABLE command. The output from the SHOW IN XML TABLE command will contain a <table> tag with a kind attribute whose value will be "Set" or "Multiset".
<Table ... kind="Set"
<Table ... kind="Multiset"
So how can i tell the difference between SET table with UPI and Multiset table with UPI?
They both will have CheckOpt = "N", and both will have UniqueFlag = "Y" in dbc.indicesv.