Using Teradata SQL: Check the size of tables in a database

If you're anything like me, you get chased by your DBAs periodically about how much space you're using on the database server and whether you really, really need all of it :-)

Obviously the DBAs have lots of tools to help them look at usage info, however us mere users often don't. However the advatnage of a SQL database is that all the info reltating to the database is stored in the database itself. So with a little bit of SQL you can get this info for yourself :-)

These pieces of SQL will help you see how much space you and your tables are actually using.

This first piece of SQL gives you a list of tables with the name of the person who created it, when they created it, who last altered it and when, when it was last accessed^[1] and finally how big the table is in MB.

SELECT
	tbl.databasename,
	tbl.TABLENAME,
	tbl.CreatorName,
	tbl.CreateTimeStamp,
	tbl.LastAlterName, 
	tbl.LastAlterTimeStamp, 
	tbl.LastAccessTimeStamp,
	SUM(currentperm)/(1024*1024) AS Table_Size_MB	-- The tables are spread over the AMPs so we have to sum the values
FROM dbc.tablesVX tbl
	LEFT JOIN dbc.tablesize siz
			ON tbl.databasename = siz.databasename
			AND tbl.TABLENAME = siz.TABLENAME
GROUP BY 1,2,3,4,5,6,7
WHERE tbl.databasename = 'JAMIES_DATABASE'
	AND tbl.TableKind <> 'V'
ORDER BY tbl.TABLENAME;

The second piece shows you how much space is allocated to your database as a whiole and how much of it is used. Result is returned in GB.

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

Apologies - I almost certainly stole this from a forum post somewhere, but unusually I didn't note down where. If you recognise this and want attribution, just drop me a note with the link and I'll be happy to oblige.

"I don't want to retire. I'm not good at crossword puzzles." - Norman Mailer.