SharePoint User Profile Export from the UserProfile Database

Just got an interesting requirement from the client I'm consulting with to extract the entire UserProfile AD user details into a CSV file.

It occurs to me I could probably get them exported from the "Central Administration" - "Manage User Profiles" section; however, as part of the requirement, they also like to have the manager details and the user reports as part of the UP extraction.

Bummer! Plan B obviously requires to write the code solution to export all the user level detail from the site-collection level, then de-duplicate them, put them all together. However, it still doesn't serve the purpose based upon the fact that a lot of UP users might not sign up as a user for all the site-collections.

As Plan B's back-up plan, I went down to the UserProfile_DB database and the UserProfile_Full table. I'm fully aware that it's just not the best practice, but under the circumstances, I just couldn't come up with a better approach than that.

Lastly, this is the query I came up with this morning in order to fulfill my task:

SELECT a.NTName, a.Employee, g.FirstName, h.LastName, 
b.Title, e.Department, a.[Office Phone], c.Email, f.Office, a.Manager
,(select top 1 f.Email from UserProfile_Full f 
	where f.NTName = a.Manager
	group by f.NTName, f.Email) as 'Manager Email'
FROM (select
a.NTName,
a.RecordID,
a.Manager,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from UserProfile_Full a, UserProfileValue b
where b.PropertyID=8 and 
a.RecordID=b.RecordID) a -- WorkPhone
left outer join
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as Title
from
  UserProfile_Full a, UserProfileValue b 
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b -- Title
on a.RecordID=b.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c -- WorkEmail
on a.RecordID=c.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d --CellPhone
on a.RecordID=d.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e --Department
on a.RecordID=e.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Office
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=11 and
a.RecordID=b.RecordID) f --Office
on a.RecordID=f.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as FirstName
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=4 and
a.RecordID=b.RecordID) g --FirstName
on a.RecordID=g.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as LastName
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=5 and
a.RecordID=b.RecordID) h --LastName
on a.RecordID=h.RecordID
order by a.NTName

As said, it wasn't a pretty solution, but when you are running out of options, it always can be leveraged as a backup-plan ;)