ReadVitamin

Useful SQL queries in Teradata Environment.

Written by admin in Wednesday, August 1st 2007 under IT

To find whether a particular column is present in which all tables across the entire database run the following query;

SELECT tablename FROM DBC.columns

WHERE columnname = <’column name’>

If you know a table name partially and want to know whether it is existing in the database or not, use the following query;

SELECT * FROM DBC.Tables

WHERE tablename like <’%table_name%’>

To know whether there is a column check present on a column, type the following SQL command;

SELECT * FROM DBC.ShowColchecks

WHERE columnname = <’column_name’>

To find out whether there is a constraint or table check present on a table, type the following SQL command;

SELECT * FROM DBC.ShowTblchecks

WHERE tablename = <’table_name’>

SELECT * FROM DBC. Table_LevelConstraints

WHERE tablename = <’table_name’>

As you might have noticed there is a prefix as DBC in all the above SELECT statements, so what is this DBC is all about. When you install Teradata, the first user that gets created is called DBC it also is a Database. Of course it is not a human user but is a system user. DBC keeps all the statistics of all the events right from the installation and hence you would see a load of information in DBC tables. In fact there are loads of table present under DBC too. To see what all tables present in the DBC database run the following command.

SELECT * FROM DBC.Tables

WHERE databasename = ‘DBC’

Popularity: 9% [?]