Please tell me a query to find the Primary key,Foreign
key,primary Index,PPI for the Database?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
What are the different table types supported by teradata?
How can we check the version of Teradata that we are using currently?
Highlight a few of the important components of Teradata?
What are the frequently used data types in teradata?
What are the joins in teradata and how many types of joins are there in teradata?
How is MLOAD Client System restarted after execution?
Difference between stored procedure and macro?
What is bteq script in teradata?
Difference between inner join and outer join?
Can you fastexport a field, which is primary key by putting equality on that key?
Why Multiload does not supports USI and supports NUSI?
How to improve the query performance without taking the collect statistics? In explain plan i have 'NO CONFIDENT'.