Hi, query for table structure?

[chechir]

Hi,

i am begginer in db2 and i have limited access to database, only querys. I need to get the table structure through query, the names and types of the fields for a specific table.

Thanks in advance.

[DavidatK]

Hi Chechir,

Give this query a try.

Code: Select all

  
  SELECT NAME                                                           
        ,COLNO                                                          
        ,COLTYPE || '('                                                 
                 || CAST(LENGTH AS CHAR(5)) || ','                      
                 || CAST(SCALE AS CHAR(5)) || ')'                       
        ,CASE WHEN NULLS = 'N'                                          
             THEN 'NOT NULL' ELSE ' ' END                               
        ,TBNAME                                                         
    FROM SYSIBM.SYSCOLUMNS                                              
   WHERE TBCREATOR = 'TEST'            -- < OR 'TEST'                   
--   AND &#40;NAME = 'YOUR_COL_NAME'      -- < COLUMN NAME \ ONLY USE ONE 
     AND &#40;TBNAME    = YOUR_TBL_NAME'  -- < TABLE NAME  /          
          &#41;                                                             
     ORDER BY TBNAME, COLNO                                             
  ;       

Be sure to only use NAME or TBNAME, not both. You can probably remove TBCREATOR, but if you do you might want to ORDER by it also

[DavidatK]

HI Again,

Here's a query for the indexes on the table

Code: Select all

  SELECT KEYS.IXNAME                                          
        ,IX.UNIQUERULE                                        
        ,KEYS.COLNAME                                         
        ,KEYS.COLNO                                           
        ,KEYS.COLSEQ                                          
        ,IX.TBNAME                                            
    FROM SYSIBM.SYSINDEXES    IX,                             
         SYSIBM.SYSKEYS       KEYS                            
   WHERE IX.TBNAME          = 'YOUR_TBL_NAME'            
     AND KEYS.IXNAME        = IX.NAME                         
   ORDER BY IX.TBNAME,                                        
            KEYS.IXNAME,                                      
            KEYS.COLSEQ                                       

[chechir]

Thanks a lot!!!

This is very useful for me, and i am seeing other tables like SYSIBM.SYSTABLES.
But i dont understand why the tables appear with a diferent name in the SQL and the aplication that i use for download the data (cwbtf.exe). For example the table BIBLIO.CLIENTES_DIRECCION in SYSIBM.SYSTABLES is "BIBLIO.DIRCLI" in cwbtf.exe.

Cheers!

[DavidatK]

The difference in names is most probably due to one being the actual table name and the other an alias for the table. This is not an uncommon practice. There are functions that you can perform on the actual table but cannot on the alias. Kind of a security feature of sorts.

Dave