When Was a DB2 Table Created and by Who?

This little bit of SQL should help you determine who created a table or when it was created…

SELECT 
CASE TAB.TYPE 
WHEN 'A' THEN 'Alias' 
WHEN 'N' THEN 'Nickname' 
WHEN 'S' THEN 'MQT' 
WHEN 'T' THEN 'Table' 
WHEN 'V' THEN 'View' 
ELSE 'OTHER: '||TAB.TYPE 
END AS T_TYPE 
,RTRIM(TAB.TABSCHEMA)||'.'||TAB.TABNAME AS TABLE_NAME 
,DATE(CREATE_TIME) AS CREATED 
,DATE(ALTER_TIME) AS ALTERED 
,TAB.COLCOUNT AS COLUMNS 
,TAB.DEFINER AS CREATED_BY 
FROM SYSCAT.TABLES TAB 
WHERE TABSCHEMA = 'XX' 
ORDER BY TABLE_NAME 
FOR READ ONLY 
WITH UR;

Leave a Reply