I've had to do this myself recently as part of a 6.2 to 7.5 migration. This query took a while to write, so figured I would post it here for all to use. If you set your results options in SQL Query Analyser to copy the column names and set results to grid, you can essentially run the query and copy and paste the results in to a CSV file.
==================================================================================================
select isNull(u1.USERNAME, '') as [USERNAME], isNull(u2.TYPE, '') as [TYPE], isNull(u2.USERTEMPLATE, '') as [USERTEMPLATE],
isNull(u1.PREFIX, '') as [PREFIX], isNull(u1.FIRSTNAME, '') as [FIRSTNAME], isNull(u1.MIDDLENAME, '') as [MIDDLENAME],
isNull(u1.LASTNAME, '') as [LASTNAME], isNull(u1.SUFFIX, '') as [SUFFIX], isNull(u1.EMAIL, '') as [EMAIL],
isNull(u1.TITLE, '') as [TITLE], isNull(u1.REGION, '') as [REGION], isNull(u1.DIVISION, '') as [DIVISION],
isNull(u1.DEPARTMENT, '') as [DEPARTMENT], isNull(u3.FIRSTNAME, '') + ' ' + isNull(u3.LASTNAME, '') as [MANAGER],
isNull(u2.ISMANAGER , '') as [ISMANAGER], isNull(a1.address1, '') as [WORKADDRESS1],
isNull(a1.address2, '') as [WORKADDRESS2], isNull(a1.city, '') as [WORKCITY],
isNull(a1.state, '') as [WORKSTATE], isNull(a1.postalcode, '') as [WORKPOSTALCODE],
isNull(a1.county, '') as [WORKCOUNTY], isNull(a1.country, '') as [WORKCOUNTRY], isNull(u1.PHONE, '') as [WORKPHONE],
isNull(u1.DIRECT, '') as [DIRECTPHONE], isNull(u1.FAX, '') as [FAX], isNull(u1.MOBILE, '') as [MOBILEPHONE],
isNull(u1.PAGERTERM, '') as [PAGERNUMBER], isNull(u1.PAGERTERM, '') as [PAGERPIN], isNull(a2.address1, '') as [HOMEADDRESS1],
isNull(a2.address2, '') as [HOMEADDRESS2], isNull(a2.city, '') as [HOMECITY], isNull(a2.state, '') as [HOMESTATE],
isNull(a2.postalcode, '') as [HOMEPOSTALCODE], isNull(a2.county, '') as [HOMECOUNTY], isNull(a2.country, '') as [HOMECOUNTRY],
isNull(u1.HOME, '') as [HOMEPHONE], isNull(u1.ACCOUNTINGUSERID, '') as [ACCOUNTINGID], isNull(u4.ASSETID, '') as [ASSETID],
isNull(u4.ASSETDESC, '') as [ASSETDESC], isNull(u4.HIREDATE, '') as [HIREDATE], isNull(u4.BIRTHDATE, '') as [BIRTHDATE],
isNull(u4.SPOUSE, '') as [SPOUSE], isNull(u4.SSN, '') as [SSN], isNull(u4.EMPLOYEENUMBER, '') as [EMPLOYEENUMBER],
isNull(u4.USERDATE1, '') as [USERDATE1], isNull(u4.USERDATE2, '') as [USERDATE2],
isNull(u4.USERDATE3, '') as [USERDATE3], isNull(u4.USERDATE4, '') as [USERDATE4],
isNull(u4.USERDATE5, '') as [USERDATE5], isNull(u4.USERTEXT1, '') as [USERTEXT1],
isNull(u4.USERTEXT2, '') as [USERTEXT2], isNull(u4.USERTEXT3, '') as [USERTEXT3],
isNull(u4.USERTEXT4, '') as [USERTEXT4], isNull(u4.USERTEXT5, '') as [USERTEXT5],
isNull(u4.USERNUM1, '') as [USERNUM1], isNull(u4.USERNUM2, '') as [USERNUM2],
isNull(u4.USERNUM3, '') as [USERNUM3], isNull(u4.USERNUM4, '') as [USERNUM4],
isNull(u4.USERNUM5, '') as [USERNUM5],isNull(cast(u4.NOTES as varchar(500)), '') as [NOTES],
isNull(u1.WINDOWSSID, '') as [WINDOWSSID],
'<%your company name here%>' as [USERCOMPANY]
from sysdba.userinfo u1
join sysdba.USERSECURITY u2 on (u1.userid = u2.userid)
left join sysdba.USERINFO u3 on (u1.userid = u2.managerid)
join sysdba.address a1 on (a1.addressid = u1.addressid)
left outer join sysdba.address a2 on (a2.addressid = u1.homeaddressid)
join sysdba.userprofile u4 on (u1.userid = u4.userid)
where u2.type in ('T', 'N') -- Note T = Web Users, N = Network. May need to add others as appropriate. |