Informix catalogs 讀取資料表及欄位資訊

 --資料表
SELECT * FROM systables WHERE tabid > 99
;
--資料表及欄位
SELECT * FROM systables a INNER JOIN syscolumns b
    ON a.tabid = b.tabid
WHERE a.tabid > 99
;
--資料表欄位及類型 (參考此dbform此篇討論 )
SELECT t.tabname,c.colno, c.colname[1,20] column,                          
    CASE coltype                                                              
        WHEN 0 THEN 'char(' || TRIM(CAST (c.collength AS CHAR(5))) || ')'     
        WHEN 1 THEN 'smallint'                                                
        WHEN 2 THEN 'integer'                                                 
        WHEN 3 THEN 'float'                                                   
        WHEN 4 THEN 'smallfloat'                                              
        WHEN 5 THEN 'decimal(' ||                                             
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) || ')'
        WHEN 6 THEN 'serial'                                                  
        WHEN 7 THEN 'date'                                                    
        WHEN 8 THEN 'money(' ||                                               
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) || ')'
        WHEN 9 THEN 'null'                                                    
        WHEN 10 THEN 'datetime'                                               
        WHEN 11 THEN 'byte'                                                   
        WHEN 12 THEN 'text'                                                   
        WHEN 13 THEN 'varchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')'  
        WHEN 14 THEN 'interval'                                               
        WHEN 15 THEN 'nchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')'    
        WHEN 16 THEN 'nvarchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')' 
        WHEN 17 THEN 'int8'                                                   
        WHEN 18 THEN 'serial8'                                                
        WHEN 19 THEN 'set'                                                    
        WHEN 20 THEN 'multiset'                                               
        WHEN 21 THEN 'list'                                                   
        WHEN 22 THEN 'row'                                                    
        WHEN 23 THEN 'collection'                                             
        WHEN 24 THEN 'rowdef'        
        WHEN 40 THEN 'lvarchar'                                                
        WHEN 256 THEN 'char(' || TRIM(CAST(c.collength AS CHAR(5))) ||        
            ') not null'                                                      
        WHEN 257 THEN 'smallint not null'                                     
        WHEN 258 THEN 'integer not null'                                      
        WHEN 259 THEN 'float not null'                                        
        WHEN 260 THEN 'smallfloat not null'                                   
        WHEN 261 THEN 'decimal('||                                            
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) ||  
            ') not null'                                                      
        WHEN 262 THEN 'serial not null'                                       
        WHEN 263 THEN 'date not null'                                         
        WHEN 264 THEN 'money(' ||                                             
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) ||  
            ') not null'                                                      
        WHEN 265 THEN 'null not null'                                         
        WHEN 266 THEN 'datetime not null'                                     
        WHEN 267 THEN 'byte not null'                                         
        WHEN 268 THEN 'text not null'                                         
        WHEN 269 THEN 'varchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||     
            ') not null'                                                      
        WHEN 270 THEN 'interval not null'                                     
        WHEN 271 THEN 'nchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||       
            ') not null'                                                      
        WHEN 272 THEN 'nvarchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||    
            ') not null'                                                      
        WHEN 273 THEN 'int8 not null'                                         
        WHEN 274 THEN 'serial8 not null'                                      
        WHEN 275 THEN 'set not null'                                          
        WHEN 276 THEN 'multiset not null'                                     
        WHEN 277 THEN 'list not null'                                         
        WHEN 278 THEN 'row not null'                                          
        WHEN 279 THEN 'collection not null'                                   
        WHEN 280 THEN 'rowdef not null'                                       
        ELSE CAST(coltype AS CHAR(10))                                        
    END datatype                                                              
FROM systables t INNER JOIN syscolumns c
  ON t.tabid = c.tabid                                        
WHERE t.tabid > 99                                    
ORDER BY 1,2                 
;