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