Please tell me a query to find the Primary key,Foreign key,primary Index,PPI f

Categories >> Software >> Data Warehouse >> Teradata
Suggest New Category

Please tell me a query to find the Primary key,Foreign
key,primary Index,PPI for the Database?

Question Posted / ramya85

5 Answers
27280 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Please tell me a query to find the Primary key,Foreign key,primary Index,PPI for the Database?..

Answer / bhanu prasad

We can find the information of all the indexes in the
system table "dbc.indices"

Is This Answer Correct ?

19 Yes

4 No

Please tell me a query to find the Primary key,Foreign key,primary Index,PPI for the Database?..

Answer / innocent

Hi Parsad,
I hope by running below query you'll get many part of your
answer.

_----------------------------------------------------------_
Select DatabaseName, TableName ,columnName,
Case When IndexType='K' Then 'Primary Key'
When IndexType='S' Then 'Secondary Index'
When IndexType='P' Then 'Primary Index'
When IndexType='Q' Then 'PPIndex'
When IndexType='J' Then 'Join Index'
End as implimented_Index
From DBC.Indices
Where TableName in
( Select distinct TableName
From DBC.Tablesize
Where DatabaseName <>'DBC'
And
CurrentPerm>0
)
Order by 1,2,3

_
Innocent

Is This Answer Correct ?

6 Yes

0 No

Please tell me a query to find the Primary key,Foreign key,primary Index,PPI for the Database?..

Answer / innocent

--Find indixes.
Select TableName,ColumnName,IndexType,UniqueFlag,IndexName
From DBC.Indices
Where tableName='mytable_name'

Is This Answer Correct ?

6 Yes

1 No

Please tell me a query to find the Primary key,Foreign key,primary Index,PPI for the Database?..

Answer / yuvaevergreen

Adding to the above,DBC.Indexes can be used to find whether
the index is PPI or join or hash or primary key.

Is This Answer Correct ?

5 Yes

0 No

Please tell me a query to find the Primary key,Foreign key,primary Index,PPI for the Database?..

Answer / prasanna

Hi Innocent,

Appreciate for your answer.

But one small correction is that tablename and database name columns are present in DBC.indices itself where as you had went for a join condition with dbc.tablesize.

so without join condition we can take both tablename and database from dbc.indices table.

Thanks
Prasanna.

Is This Answer Correct ?

0 Yes

0 No

Post New Answer