How to find the unused tables in Teradata?

Posted on by

Unused tables in Teradata

In general, DBA team in the Organization allocate limited amount of Teradata space for each user(individual/batch user). The user who creates the table in Teradata needs to delete/drop the tables if there is no usage for any of the tables. In this process, DBA team also will help us to find out those tables. We called those kind of tables as deprecated tables.

Find unused tables in Teradata

As we know that metadata details of tables are stored in DBC database, we can use DBC.tables to find the unused tables in Teradata. The DBC.tables contains the column as LastAccessTimeStamp which tells us when was the last time the table was accessed in the system.

If the tables are not accessed for the last 60 or 90 days, we can revisit those tables and drop it to make effective use of the space. Lets write the query to find unused tables in Teradata

Here dbc.tablesv is the view name of the dbc.tables. If we want to find unused tables for specific databases, we can mention the DatabaseName in the query. Finally the LastAccessTimeStamp is mentioned as CURRENT_DATE-90 which returns tables which are not accessed for the last 90 days.

Sample output

As we can see below, the below are the list of tables which are not accessed for the last 90 days in Banking_prod_db.

Find unused tables list in Teradata
Find unused tables list in Teradata

Deprecation of unused tables in Teradata

Either we can delete the records or drop the tables to save the space in Teradata. If we are doing that in the Test environment, it won’t create a big issue. But if the tables are in the production environment, we need to follow few steps to avoid any issue.

  • Perform the downstream usage analysis for the tables
  • Check with the team members as the data may be used for any analytics.
  • Get the proper approvals as per the organization process.

Once we are good to proceed the deprecation of unused tables, we can run the DROP statement as “DROP table <DatabaseName>.<TableName>;” in Teradata.

Recommended Articles