Teradata Database Object Summary

In my previous blog post I showed how to build an Object Catalog of your Teradata data warehouse. Trouble is even on moderately sized systems your Object catalog could result in tens of thousands of rows. One of my object summaries is over thirty thousand rows. This can be overwhelming if you hand it off to management since they prefer a thirty thousand foot view.

So in order to provide a higher level overview of your Teradata system you can create a view on top of your object catalog that summarizes information at the database or user level providing information such perm space allocation/consumption, average table perm space consumption, average number of rows per table, number of tables with/without stats, and a count of each object type in the database.

The following view could be extended fairly easily to include additional metrics on such as the largest and smallest tables in both row count or perm space consumption. For my needs, I didn’t need to include those items.Â The SQL can be found below:

REPLACE VIEW SysDBA.Database_Object_Overview AS
SELECT C.DatabaseName
     , CASE WHEN RowType = 'u' THEN 'User'
            WHEN RowType = 'D' THEN 'Database'
            ELSE NULL
       END DatabaseType
     , DatabaseAvailablePerm
     , SUM(TableSize) AS DatabaseSize
     , AVG(TableSize) AS AvgTableSize
     , AVG(TableRows) AS AvgTableRows
     , SUM(CASE WHEN TableMissingStats = 'Y' THEN 1 ELSE 0 END) TablesMissingStats
     , SUM(CASE WHEN TableMissingStats = 'N' THEN 1 ELSE 0 END) TablesWithStats
     , TablesWithStats/(NULLIFZERO(TableCount) * 1.000) AS PercentTableWithStats
     , COUNT(TableName) AS ObjectCount
     , SUM(CASE WHEN TableKind = 'T' THEN 1 ELSE 0 END) AS TableCount
     , SUM(CASE WHEN TableKind = 'V' THEN 1 ELSE 0 END) AS ViewCount
     , SUM(CASE WHEN TableKind = 'M' THEN 1 ELSE 0 END) AS MacroCount
     , SUM(CASE WHEN TableKind = 'P' THEN 1 ELSE 0 END) AS ProcedureCount
     , SUM(CASE WHEN TableKind = 'E' THEN 1 ELSE 0 END) AS ExternalCount
     , SUM(CASE WHEN TableKind = 'F' THEN 1 ELSE 0 END) AS FunctionCount
     , SUM(CASE WHEN TableKind = 'I' THEN 1 ELSE 0 END) AS JoinIndexCount
     , SUM(CASE WHEN TableKind = 'U' THEN 1 ELSE 0 END) AS UserDefinedTypeCount
     , SUM(CASE WHEN TableKind = 'S' THEN 1 ELSE 0 END) AS OLAPFunctionCount
     , SUM(CASE WHEN TableKind = 'R' THEN 1 ELSE 0 END) AS TableFunctionCount
     , SUM(CASE WHEN TableKind = 'G' THEN 1 ELSE 0 END) AS TriggerCount
     , SUM(CASE WHEN TableKind = 'D' THEN 1 ELSE 0 END) AS JARCount
     , SUM(CASE WHEN TableKind = 'Q' THEN 1 ELSE 0 END) AS QueueTableCount
     , SUM(CASE WHEN TableKind = 'N' THEN 1 ELSE 0 END) AS HashIndexCount
     , SUM(CASE WHEN TableKind = 'J' THEN 1 ELSE 0 END) AS JournalCount
     , SUM(CASE WHEN TableKind = 'H' THEN 1 ELSE 0 END) AS InstanceOrMethodCount
     , SUM(CASE WHEN TableKind = 'B' THEN 1 ELSE 0 END) AS CombinedAggOLAPCount
     , SUM(CASE WHEN TableKind = 'A' THEN 1 ELSE 0 END) AS AggregateFunctionCount
     , SUM(CASE WHEN TableKind = 'X' THEN 1 ELSE 0 END) AS AuthorizationCount
FROM SysDBA.Object_Catalog C
   , DBC.Dbase D
   , (SELECT DatabaseName
           , SUM(MaxPerm) AS DatabaseAvailablePerm
      FROM DBC.DiskSpace
      GROUP BY 1
     ) S
WHERE C.DatabaseName = D.DatabaseName
  AND S.DatabaseName = D.DatabaseName
GROUP BY 1,2,3
;

Share this: