How to determine the Identity column name for a table having such a column?

[ScottLLama]

I have an application that determines the schema for a table through a Windows ODBC connector. Not all attributes are accessible through GetSchema() calls so I have to augment with the querying of system tables.

My current issue is determining whether a table has an indentity (auto-increment) column and the column name. I found this link useful in determining whether a table has such a column: http://forums.teradata.com/forum/database/reset- identity-column-counter. But, it does not hint at how the column name would be determined.

Would somebody know of the way to get the column name for an identity column in a table that has one?

[VandeBergB]

You need to spend some time familiarizing yourself with the dbc views. In particular, dbc.columns, which should answer your question.  The Teradata User Documentation will assist in your quest as well.

The following code snippet will create a small table with an identity column and interrogate said view to show you how dbc.columns keeps track of the columns in the database.

This should be a great learning experience!

CREATE TABLE <INSERT db name here>.ColumnTest
, NO FALLBACK
, NO BEFORE JOURNAL
, NO AFTER JOURNAL
, CHECKSUM = DEFAULT
(IDColumn  BIGINT GENERATED always AS IDENTITY
,Column2    INTEGER
,Column3 INTEGER)
PRIMARY INDEX (IDcolumn);

SELECT * FROM dbc.COLUMNS WHERE columnname = 'idcolumn';

You'll see the datatypes, column order and some additional flags, one of which is IDColType...

[ScottLLama]

When you mentioned the Columns view, the name was so apparent that I thought that I had been there. I opened it up and looked for the test tables I had created. Lo and behold, it appears that IdColType flags the columns of interest. I wonder if GA implies Generated Always... if not, it still makes a good mnemonic. Thanks!

[VandeBergB]

If you search through the user documentation (freely downloadable from teradata.com) somewhere in there is a list of the mnemonics used in that and some other columns.l

[ScottLLama]

A full description of the Columns view can be found in the Teradata Data Dictionary document: B035-1092-111A.

A key document.

:-)