SQL to find the names of the fields

Version 1
    This document was generated from CDN thread

    Created by: GEERT DEWINTER on 04-05-2010 10:18:11 AM
    Hi,
     
    I would like to know if there exist an SQL command to find the column names that are defined for a field.
    If you do a "SELECT * FROM DEVICE;" that also the column names are displayed.
     
    Thanks a lot in advance.
    Geert

    Subject: RE: SQL to find the names of the fields
    Replied by: David Staudt on 04-05-2010 01:37:27 PM
    The return of executeSQLQuery does have the field names embedded in the XML:
     

          <axl:executeSQLQueryResponse sequence="1" xmlns:axl="http://www.cisco.com/AXL/API/7.0" xmlns:xsi="http://www.cisco.com/AXL/API/7.0">
             <return>
                <row>
                   <pkid>672e5328-403f-4aa9-a969-e2483426b682</pkid>
                   <name>MTP_2</name>
                   <description>MTP_DS-CM7</description>
                   <tkmodel>110</tkmodel>
                   <tkdeviceprotocol>6</tkdeviceprotocol>
    ...

     
    If you want to manually check, from the UCM CLI you can execute SQL directly - the result includes field names as a header:
     

    admin:run sql select first 2 pkid,name from device
    pkid                                                                          name  
    =========================== ===== 
    672e5328-403f-4aa9-a969-e2483426b682   MTP_2 
    34186aed-859f-4ee9-98a8-d3df66258778    CFB_2 


    If you need to programmatically check the schema of a table, see the 'typetable' and 'typefield' tables in the database:



          <ns:executeSQLQuery sequence="1">
             <sql>select fieldname,fieldtype from typetableinfo,typefieldinfo where typetableinfo.enum=typefieldinfo.tktableinfo and tablename='Device'</sql>
          </ns:executeSQLQuery>
    ---------------------------



          <axl:executeSQLQueryResponse sequence="1" xmlns:axl="http://www.cisco.com/AXL/API/7.0" xmlns:xsi="http://www.cisco.com/AXL/API/7.0">
             <return>
                <row>
                   <fieldname>pkid</fieldname>
                   <fieldtype>GUID</fieldtype>
                </row>
                <row>
                   <fieldname>Name</fieldname>
                   <fieldtype>string</fieldtype>
                </row>
                <row>
                   <fieldname>Description</fieldname>
                   <fieldtype>string</fieldtype>
                </row>