How to get the table size in Teradata?

Question

I have a table in Teradata. I would like to know its size. The same thing might be interesting for databases in Teradata.

How can I do it?

Answer 1

To check your table size you can check from dbc.tablesize. check the below query.

SELECT 
     A.DatabaseName
    ,A.tablename
    ,CreateTimeStamp
    ,CAST(SUM(CURRENTPERM) AS DECIMAL(18,2))/(1024*1024*1024) (TITLE 'Used(GB)')
FROM dbc.tablesize A,DBC.TABLES B
WHERE   A.TABLENAME=B.TABLENAME
    AND A.DatabaseName = B.DatabaseName 
    AND A.DatabaseName = 'your database name/schemaname/appname'
    AND A.tablename = 'your tablename'
GROUP BY 1,2,3 ORDER BY 3 DESC;  

To check data base size you can use dbc.diskspace, the query will be something like this:

SELECT 
     DatabaseName
    ,CAST(SUM(CurrentPerm) AS DECIMAL(18,5))/(1024*1024*1024) (TITLE 'Used(GB)')
    ,CAST(SUM(MaxPerm) AS DECIMAL(18,5))/(1024*1024*1024) (TITLE 'Allocated(GB)') 
    ,CAST(SUM(MaxPerm)-SUM(CurrentPerm) AS DECIMAL(18,5))/(1024*1024*1024) (TITLE 'Free(GB)')
FROM DBC.DiskSpace
WHERE DatabaseName = 'databasename'
GROUP BY 1
ORDER BY 2 Desc; 

This will give you used space, allocated space and free space available in your database.

Answer 2

Code divides ints then casts as decimal suffering truncation. Changing the cast to:

CAST(SUM(CurrentPerm) AS DECIMAL(18,5)) /(1024*1024*1024) (TITLE 'Used(GB)')

gives what you meant