Teradata - Useful SQLs
1. How to create a copy of table using an existing table including data in one line? create multiset table < NewTableDB>.<NewTableName> as <ExistingTableDB>.<ExistingTableName> with data ; 2. Some times we may need to review the table level stats like who, when table has been created, when was it last accessed etc for various reasons. In such scenarios you can use the following sql -- Table space in GB along with last access time in a specific database SELECT a.databasename, a. tablename , t.creatorname, t.CreateTimestamp, t.LastAccessTimestamp, SUM (a.currentperm)/1024**3 ( FORMAT 'zz9.99%' ) AS current_GB FROM dbc .allspace a join dbc .tablesv t on a.databasename = t.databasename and a. tablename = t. tablename WHERE -- tablename = '<tblname' AND a.databasename = '<dbname>' GROUP BY 1,2,3,4,5 ORDER BY current_GB desc ; 3. When you need to find specific SQL ...