Finding all the tables and columns on a Cisco Informix DB
Sometimes the Cisco Databases in these UC products just aren’t documented at the level I would like them. Had a friend ask today about trying to harvest some info from UCCX on a semi intelligent basis, and honestly I don’t know the database at all. Here are 2 commands you can run on any of the Cisco Appliances that use Informix to dump the tables names and a table’s column names.
To dump all the tables in Informix through the CLI on CUCM:
run sql SELECT tabname from systables
Results:
admin:run sql SELECT tabname from systables tabname ================================ systables syscolumns sysindices systabauth syscolauth sysviews sysusers sysdepend syssynonyms syssyntable sysconstraints sysreferences syschecks sysdefaults syscoldepend sysprocedures sysprocbody sysprocplan sysprocauth sysblobs sysopclstr systriggers systrigbody sysdistrib sysfragments sysobjstate sysviolations sysfragauth sysroleauth sysxtdtypes sysattrtypes sysxtddesc sysinherits syscolattribs syslogmap syscasts sysxtdtypeauth sysroutinelangs syslangauth sysams systabamdata sysopclasses syserrors systraceclasses systracemsgs sysaggregates syssequences sysdirectives sysxasourcetypes sysxadatasources sysseclabelcomponents sysseclabelcomponentelements syssecpolicies syssecpolicycomponents syssecpolicyexemptions sysseclabels sysseclabelnames sysseclabelauth syssurrogateauth sysproccolumns sysexternal sysextdfiles sysextcols sysautolocate sysfragdist GL_COLLATE GL_CTYPE VERSION sysdomains sysindexes dblcnqueue dblschemaorder aarneighborhood aardialprefixmatrix alarmusertext availdialplan axlchangenotifyclient carrierselectprofile ccaprofile ccdhosteddngroup ccdhosteddn cdrmconfig cmcinfo codeclist confidentialaccesslevel confidentialaccesslevelmatrix corsdomain credentialpolicy devicemobilitygroup devicemobilityinfo dialplan dialplandetails dirgroup dnaliassynckeymap dntracelogging dpcertificate dpemail dpemaillist dpemaillistmap dpgroup dpntpserver dptemplate enterprisephoneconfigxml enterprisesubcluster facinfo featurecontrolpolicy functionrole functionroledirgroupmap geolocation geolocationfilter geolocationpolicy grtreport grtsource grtreportsourcemap hosteduricatalog hosteduricatalogkey installmonitor lbmhubgroup ldapauthentication ldapfilter mlaparameter mlppdomain numplandcpsyn outboundappservercn pagetemplate pagetemplatedetail physicallocation remotecatalogkey remotecluster remoteclusterilsstatus remoteclusterilshostinfo remotecommoninfo remotenumplan remoteuri rtmtprofile safccdpurgeblocklearnedroutes schscript schuserclusterinfo scratch sdpattributelist secureconfig siprealm testnotify typeadminboolean typeadminerror typeadminfieldinfo typealarmseverity typeannouncementfile typeannouncements customannouncement typeapplication typeapplicationdialrule applicationdialrule typeappserver typeappservercontent typeasn1roseoidencoding typeassignmentmode typeauthenticationmode typeautoanswer typebandwidth typebarge typebatfile typebatfilesubclause typebatfrequency typebatjobstatus typebatresultstatus typebattarget typebatfunction batfileinfo typebatusage batinputdata typebillingserverprotocol billingserver typebitpos typeblfsdoption typeboolean typebriprotocol typecalheaders typecallerfiltermask typecallerid typecallinglineidentification typecallingpartyselection typecallstate typecalltreatmentonfailure typecalmode typecarrierselectcode carrierselectcodedetails typecertificate typecertificatedistribution certificate typecertificateoperation typecertificateservice certificateservicecertificatemap typecertificatestatus typecertificateverificationlevel typecfacssactivationpolicy typechangenotifysubscribe axlchangenotifysubscribe typeclass typeclockreference typeconfiginputdatagrouping typeconfiginputdata typeconnectedpbx typeconnectionusage typeconnectprotocol typecosrouting typecosroutingclassification typecountry cubacountryregionalinfo typecredential typecredentialuser credentialpolicydefault typecsuparam typecubadevice typecubaproductdisplaygroup typecucmversioninsipheader typecustomuserattribute customuserattributename typedayofmonth typedayofweek schscriptexecution typedberrors typedevicefeature typedeviceprofile typedeviceprotocol typedevicesecuritymode safsecurityprofile safclientsettings typedevicetrustmode typedialparameter typedialpattern typedialviaoffice typedigitsending typedistributealgorithm typedndoption typednusage typedpdeviceprotocol typedpjobfeature typedpjobstatus dpjob dpjobemaillistmap typedpproduct dpdevice dpdevicegroupmap dpdeviceproductcomponent dptemplatemetacomponentlist typedptracelevel dptemplatecomponentdata typedpverificationstatus dpcallrecordserver dpdeviceaccess dpdevicecallrecordservermap dpftpserver dpmailserver dptraceserver dpdevicetraceservermap typedpwebprotocol dpdevicejobcomponent typedrfregistered typedrfscript typedscpsetting typedtmfsignaling typee911locationstate typee911message typeencode typeeosuppvoicecall typefallbackcssselection typefallbacksensitivitylevel typefdlchannel typefeatureconfig featureconfig typefeaturecontrol typeforward typeframing typegatewayusage typegclear typegeolocationdevice typegkoption typeglobalnumber remoteobjectblockrule typeglobalsetting globalsetting typegroupversionstamp typehaserverstate typehash certificatehashmap typehostedroutepatternpstnrule hostedroutepattern typehotspotauthenticationmethod typehttpprofile httpprofile typehttpprofileuri httpprofileuriextension typehttpproxy typehuntalgorithm linegroup typeidsauditlevel typeilssyncstatus remoteclusteruricatalog typeinterclusterservice remoteclusterservicemapdynamic typeipaddressingmode typeipaddressingmodeprefcontrol typeipmalinemode typeipmatarget typekeepalivetimeinterval typekeyauthority typekeysize typelampblinkrate typelanguagegroup typelanguage typecubasupportedcountry typeldapdirectoryfunction typeldapserver ldapsystemconfig typeldapprotocol ldapauthenticationhost typeldapserverattr ldapserverattribute typelicensedresource typelicensingmode typelicensefeature typelicenseunit typelicensingstate typelogicalpartitionpolicy geolocationpolicymatrix typelossynetwork typematrix matrix applicationuser applicationuserdirgroupmap location locationmatrix typematrixvalue matrixmember typemedia typecodec codeclistmember typemediapayload typemixer typemobilesmartclient mobilesmartclientprofile typemohcodec typemonitor typemonthofyear typemwlpolicy typenamedisplayformat typenetworklocation typenodeusage typensfservice typenullrestriction typenumberingplan typenumplanuritype typeoperator dialplantag typeoutboundcallrollover typepacketcapturemode applicationusercapfmap typeparam typepartitionusage typepattern typepatternhandlingflaginfo typepatternprecedence typepatternrouteclass typepatternusage hosteduri remoteroutingpattern typepersonalphonenumber typephonecategory typephonefeature typephonepersonalization typephoneservice typephoneservicecategory telecasterservice telecasterserviceparameter typephoneservicedisplay typepickupnotification typepipesize typeplaceholdertag typepluginusage plugin typeportaluri typepreemption typepreferredmediasource typepresentationbit typeprichanie typepriofnumber typepriprotocol nsfprotocolservicemap typeprocessnoderole typeproductconfig typeprotocolside typeprovider typeqsig routegroup typeqsigvariant typerecordingflag typereleasecausevalue typeremoteobject typereplicationstatus typereset callingsearchspace blockingcsslookup callmanagergroup dialrules dialrulespatternmap digitdiscardinstruction digitdiscardinstructionmember externalcallcontrolprofile incomingtransformationprofile mediaresourcegroup mediaresourcelist mediaresourcelistmember mrgmediaaccesspermission patternparametermap recordingprofile region regionmatrix resourceprioritynamespace resourceprioritydefaultnamespace resourceprioritynamespacelist resourceprioritynamespacemap routefilter routefiltercosroutingmap routefiltermember softkeytemplate softkeytemplatedefault typeresethandshake typeresource functionroleresourcemap treecontrolgroupnodes typeresourceaction typerevertpriority typeringsetting typerisclass typerisstatus typeroutingdatabasecachetimer typersvpoversip typertmtreportingservice typerule typeruleinfo typesafservice safservices typeschciscotacdestination schapplication typescheduleunit typesdpattributehandling sdpattribute sdpattributeallowedvalues typesecuritypolicy typeserversecuritymode typeservicecategory typeservicegrouping typeservicerestriction typesilencesuppressionthreshold typesipassertedtype typesipbandwidthmodifier typesipcodec typesipidentityblend typesipprivacy typesiprel1xxoptions typesipreroute typesipscripterrorhandling sipnormalizationscript sipnormalizationscriptchunk typesipsessionrefreshmethod typesiptrunkcalllegsecurity typesite typespa8800port typesrstoption srst typessomode processnode applicationusercapfmapdynamic appserver appserverapplicationusermap appserverinfo certificateprocessnodemap componentversion dhcpserver dhcpsubnet enterprisenode lbmgroup callmanager callmanagergroupmember licenseserver licensedistributionused licenseinfo processnodeaudit processnodesec processnodesecack replicationdynamic safclientcmnodemap typestartdialprotocol typestatus networkaccessprofile httpproxyexception typestringformat typestringformatrule typesubclass typemodel devicereset phonetemplate typephonetemplateusage typeproduct cubaproductsupport mgcp gatewaynetworkdetail productcapabilities typeconnection provider typeconnectiondevice typelicensedresourceproductmap typemgcpslotmodule typemgcpvic gatewaylayout mgcpslotconfig typephonefeaturecubaproductmap typesubpatternusage typesupportsfeature productsupportsfeature supportsfeatureattributemap typesyslogmessage remotesyslog remotesyslogappservermap typesystemfeature typetableinfo typefieldinfo typebatfunctionfieldinfomap typeplaceholdertagfieldinfomap typerelatedfieldinfo typeremotedestinationusage remotedestinationusagemodelmap typetaskstatus rtmtreportingdynamic typetelnetlevel typeterminal typetimeofday typetimeschedulecategory typetimezone datetimesetting typetracelevelgroups typeservice alarmmonitorcapabilities processconfig processconfigdefaults processnoderoleservicemap processnodeservice alarmconfig drfcomponent drfscript softkeytemplateservicemap troubleshootingtrace typesoftkey softkeyset typefeature modelfeaturemap phonebutton typetracelevelgrouping typetransport typetrunk typetrunkdirection typetrunklevel typetrunkpad typetrunkselectionorder typetrunkservice typetrustrole certificatetrustrolemap typetunneledprotocol typeucservice typeucproduct typeucproductappservermap typeucproductconnectprotocolmap typeuridisambiguationpolicy typeusageprofilefeature typeuseentity typeuseragentserverheaderinfo typeuserassociation typeusernotificationsection typeusernotificationmessage typeuserprofile typeusntype typev150sdpfilter securityprofile typevideocalltrafficclass typeviprfilterelement typevmavoidancepolicy typevpnclientauthentication typewebpage typewebpagedisplay typewebpageinfo typeadminfieldinfomap typewebpagesection typewifiauthenticationmethod typewififrequency typewincharset typeuserlocale commondeviceconfig customannouncementlocale e911messages ivruserlocale localestringformatrulemap mohaudiosource pnpselectedlanguage tapsuserlocale typewlanprofilechanges typeyellowalarm typezerosuppression typezzconfbridge typezzdndcontrol typezzdtmfdblevel typezznetworkmediatype typezzntpmode ntpserver ntpserverdatetimesettingmap typezzpreff typezzuserinfo sipprofile ucservice ucserviceprofile ucserviceprofiledetail ucserviceprofiledetailxml ucservicexml ucuserprofile defaultucuserprofile featuregrouptemplate directorypluginconfig directorypluginattribute directorypluginconfigdirgroupmap directorypluginhost directorypluginpoollist directorypluginroutingdatabase directorypluginschedule enduser callerfilterlist callerfilterlistmember credential credentialdynamic credentialhistory crsapplication crsuserclusterspecific customuserattributedata enduserappservermap endusercapfmap endusercapfmapdynamic enduserdirgroupmap enduserlicense endusermlppauthentication endusernotification ipmamanagerassistant ipmauser licensingresourceusage personaladdressbook personalphonebook preferences remotedestinationhistorydynamic spokenname timeperiod timeschedule routepartition callingsearchspacemember ccdrequestingserviceprofile fallbackprofile remoteobjectpartitionrule timescheduletimeperiodmap todaccess todaccesssetting usn usnreplset usnsyncservice usntombstoneuri vipre164transformation viprexcludeddidpatterngroup viprfiltergroup viprfilterelement viproffpathserver viprpublisheddidpatterngroup viprpublisheddidpattern viprserver vipruri voicemessagingpilot voicemessagingprofile vpngateway vpngatewaycertificatemap vpngroup vpngroupvpngatewaymap vpnprofile webpagesection wifihotspotprofile commonphoneconfig commonphoneconfigxml usageprofile usageprofileendusermember usageprofileproductspecific usageprofiletelecasterservicemap wirelesslanprofile wirelesslanprofilegroup devicepool defaults device analogaccess analogaccessport applicationuserdevicemap carrierselectprofiletrunkmap ccdadvertisingserviceprofile deviceaddonmodulemap devicecerdynamic devicefeaturemember devicehlogdynamic devicemanagementdynamic devicemanagementinfo devicemobilitydynamic devicepooldevicemobilityinfomap devicepoolroutegroupmap deviceprivacydynamic deviceprovidermap devicerelatedversionstamp devicetftpdynamic devicexml16k devicexml4k devicexml8k digitalaccessbri digitalaccesspri digitalaccesst1 digitalaccesst1port dmmsdevice dnddynamic emccdynamic emergencylocidnumber emergencylocidnumberdynamic emremotedynamic enduserdevicemap extensionmobilitydynamic gatekeeper h323device h323trunkdestination httpinterfaceaddress imsintegratedmobile interclusterserviceprofile mediamixer mediaresourcegroupmember mgcpdevicemember mohserver mohservermulticastinfo numplan alternatenumber applicationusernumplanmap batjob batfileinfojobmap batinputdatajobmap batjobresults blfdirectedcallpark blfspeeddial callforwardalloverride callforwarddynamic callforwardhistorydynamic destinationcodecontrol deviceautoreg devicenumplanmap devicenumplanmapendusermap endusernumplanmap huntpilotqueue intercomdynamic ipmalineinfo ipmastaticdata linegroupnumplanmap mobilityprofile nsfinformationelement numplanappservermap numplandynamic numplansubpatternusagemap numplanuri pickupgroup pickupgroupendusermap pickupgrouplinemap pickupgroupmember pickupgroupprocessnodemapdynamic recordingdynamic registrationdynamic remotedestination devicenumplanmapremdestmap remotedestinationdynamic routegroupdevicemap routelist safservicetrunkmap sipdevice devicesipdevicemap siptrunkdestination site siteroutepartitionmember speeddial tapssecurenumplan telecaster telecastersubscribedservice telecastersubscribedparameter ucuserprofiledevicemap ucuserprofilenumplanmap viprservice viprserviceccmexternalipmap viprservicedidpatterngroupmap viprvalidateddid viprurivalidateddidmap vohserver wirelesslanprofilegroupmember wsmdevice wwwcookie xmldisplayinstancerulemap device_seq devnumplanmap_seq location_seq cup_system_seq axldbchangequeue denseregiondata_vw vs_view
To dump all the columns in Tables ‘Device’ through the CLI on CUCM:
run sql SELECT TRIM(c.colname) AS table_dot_column FROM "informix".systables AS t, "informix".syscolumns AS c WHERE t.tabname = 'device' AND t.tabtype = 'T' AND t.tabid = c.tabid table_dot_column
Results:
admin:run sql SELECT TRIM(c.colname) AS table_dot_column FROM "informix".systables AS t, "informix".syscolumns AS c WHERE t.tabname = 'device' AND t.tabtype = 'T' AND t.tabid = c.tabid table_dot_column ===================================== pkid name description tkmodel tkdeviceprotocol tkprotocolside specialloadinformation fkdevicepool fkphonetemplate fkcallingsearchspace ctiid tkclass fkprocessnode defaultdtmfcapability fklocation tkproduct dialplanwizardgenid deviceleveltraceflag fkenduser allowhotelingflag tkdeviceprofile ikdevice_defaultprofile fkmediaresourcelist userholdmohaudiosourceid networkholdmohaudiosourceid unit subunit tkcountry tkuserlocale tkproduct_base fkcallingsearchspace_aar fkaarneighborhood fksoftkeytemplate retryvideocallasaudio routelistenabled fkcallmanagergroup tkstatus_mlppindicationstatus tkpreemption tkstatus_builtinbridge mtprequired tkqsig tkpacketcapturemode packetcaptureduration authenticationstring tkcertificatestatus upgradefinishtime fkmlppdomain transmitutf8 ignorepi tknetworklocation v150modemrelaycapable tkcertificateoperation fksecurityprofile fkdialrules fkcallingsearchspace_reroute fkcallingsearchspace_refer unattended_port tkdtmfsignaling requiredtmfreception publickey fksipprofile rfc2833disabled allowcticontrolflag datetimeinserted sshpassword sshuserid fkcallingsearchspace_restrict fkmatrix_presence fkcommonphoneconfig tkkeyauthority tksipcodec_mtppreferredorigcodec md5hash srtpallowed isstandard resettoggle tkreset versionstamp fkcommondeviceconfig huntlistforvm remotedevice tkstatus_devicemobilitymode dndtimeout tkdndoption tkringsetting_dnd isdualmode fkcallingsearchspace_cgpntransform fkenduser_mobility tkoutboundcallrollover tkphonepersonalization tkstatus_joinacrosslines tkbarge tkstatus_usetrustedrelaypoint istrustedrelaypoint srtpfallbackallowed ispaienabled isrpidenabled tksipprivacy tksipassertedtype fkcallingsearchspace_cdpntransform usedevicepoolcdpntransformcss nationalprefix internationalprefix unknownprefix subscriberprefix usedevicepoolcgpntransformcss ikdevice_primaryphone tkstatus_audiblealertingidle tkstatus_audiblealertingbusy isactive tkphoneservicedisplay isprotected fkmobilesmartclientprofile tkstatus_alwaysuseprimeline tkstatus_alwaysuseprimelineforvm callednationalprefix calledinternationalprefix calledunknownprefix calledsubscriberprefix callednationalstripdigits calledinternationalstripdigits calledunknownstripdigits calledsubscriberstripdigits fkcallingsearchspace_callednational fkcallingsearchspace_calledintl fkcallingsearchspace_calledunknown fkcallingsearchspace_calledsubscriber hotlinedevice fkgeolocation fkgeolocationfilter_lp sendgeolocation nationalstripdigits internationalstripdigits unknownstripdigits subscriberstripdigits fkcallingsearchspace_cgpnnational fkcallingsearchspace_cgpnintl fkcallingsearchspace_cgpnunknown fkcallingsearchspace_cgpnsubscriber usedevicepoolcalledcssnatl usedevicepoolcalledcssintl usedevicepoolcalledcssunkn usedevicepoolcalledcsssubs pstnaccess fkvipre164transformation usedevicepoolcgpntransformcssnatl usedevicepoolcgpntransformcssintl usedevicepoolcgpntransformcssunkn usedevicepoolcgpntransformcsssubs fkfeaturecontrolpolicy runonallnodes enableixchannel tkdevicetrustmode usedevicepoolrdntransformcss fkcallingsearchspace_rdntransform enablebfcp requirecerlocation usedevicepoolcgpningressdn fkcallingsearchspace_cgpningressdn earlyoffersupportforvoicecall enablegatewayrecordingqsig calreference tkcalmode ndescription msisdn fkwirelesslanprofilegroup enablecallroutingtordwhennoneisactive fkwifihotspotprofile ifx_replcheck
One response to “Finding all the tables and columns on a Cisco Informix DB”
I know this is a really old posting, but figured I’d share anyways. You can also retrieve a table listing via ‘show tech systables’ which is less to type for lazy typists. Also, I prefer to get sample data along with the columns for a given table (i.e. ‘run sql select first 1 * from device’). Everyone has their own methods I guess, but good info anyways. Thanks for posting.