TeradataTip5 – How to find join indexes defined on your table

If you try to drop or mload a table that has a join index defined on it, Teradata will give you an error:

5467:  Cannot drop or MLOAD a table with join or hash indexes. 

To find the join indexes that are defined on your table, run the following query:

SELECT  * FROM dbc.indices 
WHERE indextype='J'
     AND databasename='your_database_name'
     AND tablename='your_table_name'
ORDER BY indexname,columnposition;

Tags:

Leave a Reply

You must be logged in to post a comment.