As javalenc's post suggests there is a data dictionary with CUCM 5.x that can be a reference resource but there are a decent number of changes between the 4.x data dictionary and 5.x that you should double check before building extensive queries.
How I learned the 4.x SQL database was by using SQL query manager and looking at the table columns. I found many columns to be self explanatory.
Now, for your specific question. You need to deal with the NumPlan, Device, DeviceNumPlanMap, and RoutePartition. The NumPlan table contains all digit patterns. Patterns in the table are not unique but patterns and route partition pairs are.
Device contains all devices such as IP phones, gateways, even Route Lists. Yes, route lists are devices in CUCM 4.1 and later.
DeviceNumPlanMap is the table that matches IP phone line appearances to directory numbers. Specifically, it maps Device elements to NumPlan elements.
RoutePartition isn't necessarily required to build your query but it will resolve guid's in the NumPlan table to an actual name. Which you will appreciate rather quickly.
Some query examples:
select d.name as 'Device', d.Description as 'Description',
n.dNOrPattern as 'DN', rp.name as 'Partition'
from Device as d
inner join DeviceNumPlanMap as dmap on dmap.fkDevice = d.pkid
inner join NumPlan as n on n.pkid = dmap.fkNumPlan
inner join RoutePartition as rp on rp.pkid = n.fkRoutePartition
where d.name like 'SEP%'
order by n.dNOrPattern, rp.name
The 'where' clause is getting IP phones for you (but not device profiles, or analog stations, etc.). There are other ways to get phones only such as using values in the Device.tkDeviceProtocol, Device.tkClass, or NumPlan.tkPatternUsage fields. Play around and see what works for you
Some primer info.
In a given table any column with the name tk<*name*> is a reference to an enumerator in a type table. To find out which table simply substitute type for tk. So tkClass in the Device table is referring to a value in the enum column of the typeClass table.
In a given table any column with the name fk<*name*> is a reference to a pkid in another table. Again, to find out which table simply drop the fk. So fkRoutePartition in the NumPlan table is referring to the pkid column in the RoutePartition table.
This is consistent throughout the database.
Keep in mind that if you get into things like hunting down Calling Search spaces. You will find multiple references to the CSS table in a single row of the NumPlan table. In these cases you will find the convention fkCallingSearchSpace_. The gives you a clue as to what the CSS is used for and again fkCallingSearchSpace is the pkid in CallingSearchSpace.
Hope that helps.
Regards,
Bill
