All Forums Database
jesse.eichar 5 posts Joined 03/11
29 Mar 2011
Table metadata inspection

Hi,

I need to find a few key pieces of metadata about a table's columns. I am using JDBC so the information can be obtained using SQL or jdbc methods like getPrimaryKey etc...

Information needed:
- Sequences attached to a column.
- If a column is a primary key
- If several columns make a primary key (and which columns they are)

I am investigating getPrimary key for the two primary key issues but I have no idea about sequences right now.

Any tips are appreciated.

kostek 29 posts Joined 11/09
30 Mar 2011

Hi

- Sequences attached to a column:

select * from dbc.columns where DatabaseName='YOUR_DATABASE' and TableName='YOUR_TABLE';

in ColumnName You have columns in the table and in the IdColType You will find info about sequence: 'GA' or 'GD' depending on definition.

- If a column is a primary key
- If several columns make a primary key (and which columns they are)

Those You find in dbc.indices view

select * from dbc.indices where DatabaseName='YOUR_DATABASE' and TableName='YOUR_TABLE'

IndexType column says is it Primary Key or Index('K' or 'P') and in ColumnPosition You will find position of the columns in key or index

cheers

jesse.eichar 5 posts Joined 03/11
31 Mar 2011

for primary key is it recommended to use the select version shown above or the jdbc getPrimaryKey method? They both seem to work to me.

tomnolan 401 posts Joined 01/08
31 Mar 2011

The short answer is that you should use what works best for your application.

The Teradata JDBC Driver implements the DatabaseMetaData methods by querying the Data Dictionary views, and in some cases, executing HELP COLUMN and/or HELP TYPE commands.

All the information available from the DatabaseMetaData methods could instead be obtained by your application using SQL commands.

The benefit of using the DatabaseMetaData methods is that your application can be vendor-neutral or vendor-independent.

The drawback of using the DatabaseMetaData methods is that your application cannot obtain extra vendor-specific information outside of what is provided by the DatabaseMetaData methods. If your application needs more information that what is provided by the DatabaseMetaData methods, then your application should query the Data Dictionary directly.

You must sign in to leave a comment.