List tables in all databases of the Teradata server

Article for: SQL Server MySQL MariaDB

Queries below list tables in all databases of the Teradata server.

Query

A. Tables in all databases

SELECT  DatabaseName,
        TableName,
        CreateTimeStamp,
        LastAlterTimeStamp
FROM    DBC.TablesV
WHERE   TableKind = 'T'
ORDER BY    DatabaseName,
            TableName;

B. Only tables in user databases (excludes system databases)

SELECT  DatabaseName,
        TableName,
        CreateTimeStamp,
        LastAlterTimeStamp
FROM    DBC.TablesV
WHERE   TableKind = 'T'
AND     DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC', 'SQLJ', 
'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 'SYSSPATIAL', 
'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TD_SYSFNLIB', 
'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD', 'TDStats', 'tdwm')
ORDER BY    DatabaseName,
            TableName;

Columns

  • DatabaseName - database name
  • TableName - table name
  • CreateTimeStamp - date & time when table was created
  • LastAlterTimeStamp - date & time when table was last modified by using an ALTER statement

Rows

  • One row represents one data table in the database
  • Scope of rows: all tables in all databases the Teradata server
  • Ordered by database and table name

Notes

Query B. may not contain all system databases as this list depends on the Teradata Database configuration.

Sample results

A. Tables in all databases

B. Only tables in user databases (excludes system databases)

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

0
There are no comments. Click here to write the first comment.