Enumerate all columns reachable from a given seed table

Have you often wanted to enumerate all the tables (and their columns) that can be reached via joins (or chained joins) from a given seed table? I have. Here's my solution entirely in Transact-SQL (SQL 2005 or 2008 only) using recursive common table expressions.

   1:  Create procedure EnumAllColumns 

   2:  (

   3:      @seedTable nVarChar(100) = 'Projects'

   4:  )

   5:  as

   6:   

   7:  With cte(PrimaryEntity,PrimaryTable,RelatedEntity,RelatedTable,Recurse,[Path]) as

   8:  (

   9:      -- Select the root level

  10:      Select      distinct 

  11:                  r.Entity    as [PrimaryEntity], 

  12:                  r.Name        as [PrimaryTable],

  13:                  f.Entity    as [RelatedEntity], 

  14:                  f.Name        as [RelatedTable],

  15:                  1            as [Recurse],

  16:                  convert(nVarChar(800), r.Entity + '.' + f.Entity) as [Path]

  17:      from        sysForeignKeys a

  18:   

  19:      inner join -- table containing foreign key

  20:      (

  21:          Select    id, [name], 

  22:                  case when [name] like '%ies' then 

  23:                      left([name], len([name]) - 3) + 'y' 

  24:                  when [name] like '%s' then 

  25:                      left([name],len([name]) - 1) 

  26:                  else

  27:                      [name]

  28:                  end as [Entity]

  29:          from    sysObjects 

  30:      ) as f on    f.id = a.fKeyId

  31:   

  32:      inner join -- table containing foreign key

  33:      (

  34:          Select    id, [name],

  35:                  case when [name] like '%ies' then 

  36:                      left([name], len([name]) - 3) + 'y' 

  37:                  when [name] like '%s' then 

  38:                      left([name],len([name]) - 1) 

  39:                  else

  40:                      [name]

  41:                  end as [Entity]

  42:          from    sysObjects 

  43:      ) as r on    r.id = a.rKeyId

  44:   

  45:                  -- ignore subsequent columns of multi-column relations

  46:      where        a.keyNo = 1 

  47:          and        r.Name = @seedTable

  48:      -- end of root level

  49:   

  50:      union all

  51:   

  52:      -- select the child levels

  53:      Select      r.Entity as [PrimaryEntity],

  54:                  r.Name as [PrimaryTable],

  55:                  f.Entity as [RelatedEntity],

  56:                  f.Name as [RelatedTable],

  57:                  cte.recurse + 1,

  58:                  convert(nVarChar(800), cte.Path + '.' + f.Entity)

  59:      from        sysForeignKeys a

  60:   

  61:      inner join -- table containing foreign key

  62:      (

  63:          Select    id, [name], 

  64:                  case when [name] like '%ies' then 

  65:                      left([name], len([name]) - 3) + 'y' 

  66:                  when [name] like '%s' then 

  67:                      left([name],len([name])-1) 

  68:                  else

  69:                      [name]

  70:                  end as [Entity]

  71:          from    sysObjects 

  72:      ) as f on    f.id = a.fKeyId

  73:   

  74:      inner join -- table containing foreign key

  75:      (

  76:          Select    id, [name], 

  77:                  case when [name] like '%ies' then 

  78:                      left([name], len([name]) - 3) + 'y' 

  79:                  when [name] like '%s' then 

  80:                      left([name],len([name])-1) 

  81:                  else

  82:                      [name]

  83:                  end as [Entity]

  84:          from    sysObjects 

  85:      ) as r on    r.id = a.rKeyId

  86:   

  87:      inner join    cte 

  88:          on        cte.relatedTable = r.[name]

  89:   

  90:                  -- ignore subsequent columns of multi-column relations

  91:      where        a.keyNo = 1 

  92:                  -- restrict recursion to a level that SQL will be happy with

  93:          and        cte.recurse < 100 

  94:                  -- ignore same-table references (could cause max recursion level to be hit)

  95:          and        r.[name] <> f.[name] 

  96:   

  97:      -- end of child level

  98:  )

  99:  Select  [Path] + '.' + col

 100:   

 101:  from  -- Select the tables

 102:  (

 103:      Select      *

 104:      from        cte

 105:   

 106:      union

 107:   

 108:      Select        null, null, @seedTable, @seedTable, 0, @seedTable

 109:  ) as cte

 110:   

 111:  inner join -- join to the columns

 112:  (

 113:      Select        col.[name] as col, tbl.[name] as tbl

 114:      from        sysColumns col

 115:      inner join    sysObjects tbl

 116:          on        tbl.id = col.id

 117:  ) as cols on    cte.relatedTable = tbl

 118:   

 119:  order by        recurse, 1

The above query produces output similar to this:-

• Project.pActive
• Project.pCheckedOutByUserID
• Project.pDate
• Project.pFinish
• Project.pID
• Project.pLastAccessed
• Project.pOwnerID
• Project.pProjectName
• Project.pProjectNotes
• Project.pSecurity
• Project.pStart
• Project.ActionLog.alActionMsg
• Project.ActionLog.alActionSource
• Project.ActionLog.alActionType
• Project.ActionLog.alID
• Project.ActionLog.alProjectID
• Project.ActionLog.alTimeStamp
• Project.ActionLog.alUserID
• Project.Appraisal.asAcqVAT
• Project.Appraisal.asAddress
• Project.Appraisal.asAnalysedOnCosts
• Project.Appraisal.asAppraisalBy
• Project.Appraisal.asAppraisalName
• Project.Appraisal.asAppraisalPath
• Project.Appraisal.asBidNo
• Project.Appraisal.asCapInterest
• Project.Appraisal.asCashflowID
• Project.Appraisal.asCompliesSDS
• Project.Appraisal.asComRentedUnits
• Project.Appraisal.asComSales
• Project.Appraisal.asComSalesUnits
• Project.Appraisal.asDate
• Project.Appraisal.asDateofAppraisal
• Project.Appraisal.asFileRef
• Project.Appraisal.asFirstEvent
• Project.Appraisal.asFirstSalesDate
• Project.Appraisal.asFirstWorksPayment
• Project.Appraisal.asFundingYear
• Project.Appraisal.asHCSHG
• Project.Appraisal.asID
• Project.Appraisal.asImportDate
• Project.Appraisal.asImportFlags
• Project.Appraisal.asLA
• Project.Appraisal.asLASHG
• Project.Appraisal.asLastAcqPayment
• Project.Appraisal.asLastEvent
• Project.Appraisal.asLastSalesDate
• Project.Appraisal.asLastWorksPayment
• Project.Appraisal.asLCHOUnits
• Project.Appraisal.asNPV
• Project.Appraisal.asNPVDiscountPeriod
• Project.Appraisal.asNPVDiscountRate
• Project.Appraisal.asNPVLabel
• Project.Appraisal.asNPVNettRent
• Project.Appraisal.asOpeningLoan
• Project.Appraisal.asOtherCostsLine1
• Project.Appraisal.asOtherCostsLine2
• Project.Appraisal.asOtherDescription
• Project.Appraisal.asOtherDescriptionLabel
• Project.Appraisal.asOtherGrantLine1
• Project.Appraisal.asOtherGrantLine2
• Project.Appraisal.asPFSubsidyLine1
• Project.Appraisal.asPFSubsidyLine2
• Project.Appraisal.asPFSubsidyLine3
• Project.Appraisal.asProjectID
• Project.Appraisal.asPublicSubsidy
• Project.Appraisal.asRCGF
• Project.Appraisal.asRentedUnits
• Project.Appraisal.asRentPeriods
• Project.Appraisal.asRentToHomebuyUnits
• Project.Appraisal.asResSales
• Project.Appraisal.asResSalesUnits
• Project.Appraisal.asSchemeCostIndex
• Project.Appraisal.asSchemeType
• Project.Appraisal.asTotalAcq
• Project.Appraisal.asTotalFees
• Project.Appraisal.asTotalWorks
• Project.Appraisal.asTSC
• Project.Appraisal.asVersion
• Project.Appraisal.asWorksVAT
• Project.Appraisal.asYield
• Project.Note.DueDate
• Project.Note.IsComplete
• Project.Note.NoteID
• Project.Note.NoteText
• Project.Note.OriginatorID
• Project.Note.OriginatorKey
• Project.Order.orCashflowID
• Project.Order.orDate
• Project.Order.orDatePaid
• Project.Order.orID
• Project.Order.orOrgID
• Project.Order.orProjectCostCodeID
• Project.Order.orProjectID
• Project.Order.orRef
• Project.Order.orScope
• Project.Order.orSupplyEnd
• Project.Order.orSupplyStart
• Project.Order.orValue
• Project.ProjectCashflow.pcfCashflowName
• Project.ProjectCashflow.pcfDateIntoMgmt
• Project.ProjectCashflow.pcfID
• Project.ProjectCashflow.pcfInterestRate
• Project.ProjectCashflow.pcfLastEdited
• Project.ProjectCashflow.pcfLastEditedBy
• Project.ProjectCashflow.pcfPeriodEnd
• Project.ProjectCashflow.pcfPeriodEndDate
• Project.ProjectCashflow.pcfProjectID
• Project.ProjectCashflow.pcfSchemeCode
• Project.ProjectCashflow.pcfTransFile
• Project.ProjectInfoLink.pilID
• Project.ProjectInfoLink.pilProjectID
• Project.ProjectInfoLink.pilProjectInfoID
• Project.ProjectInfoLink.pilValue
• Project.ProjectOrganisation.potID
• Project.ProjectOrganisation.potOrgCatLinkID
• Project.ProjectOrganisation.potProjectID
• Project.ProjectWorkflow.pwfID
• Project.ProjectWorkflow.pwfOrder
• Project.ProjectWorkflow.pwfProjectID
• Project.ProjectWorkflow.pwfWorkflowName
• Project.Property.prAppraisalUnitID
• Project.Property.prCaption
• Project.Property.prID
• Project.Property.prIsParent
• Project.Property.prOrder
• Project.Property.prParentPropertyID
• Project.Property.prProjectID
• Project.Timesheet.tsCategoryID
• Project.Timesheet.tsDate
• Project.Timesheet.tsHours
• Project.Timesheet.tsID
• Project.Timesheet.tsNotes
• Project.Timesheet.tsProjectID
• Project.Timesheet.tsUserID
• Project.Appraisal.AppraisalDescription.adAppraisalID
• Project.Appraisal.AppraisalDescription.adDescription
• Project.Appraisal.AppraisalDescription.adID
• Project.Appraisal.AppraisalUnitDetail.audAppraisalID
• Project.Appraisal.AppraisalUnitDetail.audBeds
• Project.Appraisal.AppraisalUnitDetail.audCatalystDevType
• Project.Appraisal.AppraisalUnitDetail.audComRent
• Project.Appraisal.AppraisalUnitDetail.audDwelling
• Project.Appraisal.AppraisalUnitDetail.audEquityShare
• Project.Appraisal.AppraisalUnitDetail.audExtended
• Project.Appraisal.AppraisalUnitDetail.audFlat
• Project.Appraisal.AppraisalUnitDetail.audFloorArea
• Project.Appraisal.AppraisalUnitDetail.audGroundRent
• Project.Appraisal.AppraisalUnitDetail.audHabRooms
• Project.Appraisal.AppraisalUnitDetail.audHCProductType
• Project.Appraisal.AppraisalUnitDetail.audHCTargetRent
• Project.Appraisal.AppraisalUnitDetail.audID
• Project.Appraisal.AppraisalUnitDetail.audJan99
• Project.Appraisal.AppraisalUnitDetail.audNewBuild
• Project.Appraisal.AppraisalUnitDetail.audOpenMarketValue
• Project.Appraisal.AppraisalUnitDetail.audOtherCharges
• Project.Appraisal.AppraisalUnitDetail.audOtherDescLabel
• Project.Appraisal.AppraisalUnitDetail.audOtherDescValue
• Project.Appraisal.AppraisalUnitDetail.audPersonalCharges
• Project.Appraisal.AppraisalUnitDetail.audPersons
• Project.Appraisal.AppraisalUnitDetail.audResRent
• Project.Appraisal.AppraisalUnitDetail.audServiceCharges
• Project.Appraisal.AppraisalUnitDetail.audShared
• Project.Appraisal.AppraisalUnitDetail.audSheltered
• Project.Appraisal.AppraisalUnitDetail.audStoreys
• Project.Appraisal.AppraisalUnitDetail.audUnits
• Project.Appraisal.AppraisalUnitDetail.audUnitType
• Project.Appraisal.Note.DueDate
• Project.Appraisal.Note.IsComplete
• Project.Appraisal.Note.NoteID
• Project.Appraisal.Note.NoteText
• Project.Appraisal.Note.OriginatorID
• Project.Appraisal.Note.OriginatorKey
• Project.ProjectCashflow.Allocation.alActualAllocDate
• Project.ProjectCashflow.Allocation.alActualApprovalDate
• Project.ProjectCashflow.Allocation.alActualBidDate
• Project.ProjectCashflow.Allocation.alAllocationName
• Project.ProjectCashflow.Allocation.alCashflowID
• Project.ProjectCashflow.Allocation.alForecastAllocDate
• Project.ProjectCashflow.Allocation.alForecastApprovalDate
• Project.ProjectCashflow.Allocation.alForecastBidDate
• Project.ProjectCashflow.Allocation.alID
• Project.ProjectCashflow.Allocation.alRegion
• Project.ProjectCashflow.Allocation.alTenure
• Project.ProjectCashflow.Appraisal.asAcqVAT
• Project.ProjectCashflow.Appraisal.asAddress
• Project.ProjectCashflow.Appraisal.asAnalysedOnCosts
• Project.ProjectCashflow.Appraisal.asAppraisalBy
• Project.ProjectCashflow.Appraisal.asAppraisalName
• Project.ProjectCashflow.Appraisal.asAppraisalPath
• Project.ProjectCashflow.Appraisal.asBidNo
• Project.ProjectCashflow.Appraisal.asCapInterest
• Project.ProjectCashflow.Appraisal.asCashflowID
• Project.ProjectCashflow.Appraisal.asCompliesSDS
• Project.ProjectCashflow.Appraisal.asComRentedUnits
• Project.ProjectCashflow.Appraisal.asComSales
• Project.ProjectCashflow.Appraisal.asComSalesUnits
• Project.ProjectCashflow.Appraisal.asDate
• Project.ProjectCashflow.Appraisal.asDateofAppraisal
• Project.ProjectCashflow.Appraisal.asFileRef
• Project.ProjectCashflow.Appraisal.asFirstEvent
• Project.ProjectCashflow.Appraisal.asFirstSalesDate
• Project.ProjectCashflow.Appraisal.asFirstWorksPayment
• Project.ProjectCashflow.Appraisal.asFundingYear
• Project.ProjectCashflow.Appraisal.asHCSHG
• Project.ProjectCashflow.Appraisal.asID
• Project.ProjectCashflow.Appraisal.asImportDate
• Project.ProjectCashflow.Appraisal.asImportFlags
• Project.ProjectCashflow.Appraisal.asLA
• Project.ProjectCashflow.Appraisal.asLASHG
• Project.ProjectCashflow.Appraisal.asLastAcqPayment
• Project.ProjectCashflow.Appraisal.asLastEvent
• Project.ProjectCashflow.Appraisal.asLastSalesDate
• Project.ProjectCashflow.Appraisal.asLastWorksPayment
• Project.ProjectCashflow.Appraisal.asLCHOUnits
• Project.ProjectCashflow.Appraisal.asNPV
• Project.ProjectCashflow.Appraisal.asNPVDiscountPeriod
• Project.ProjectCashflow.Appraisal.asNPVDiscountRate
• Project.ProjectCashflow.Appraisal.asNPVLabel
• Project.ProjectCashflow.Appraisal.asNPVNettRent
• Project.ProjectCashflow.Appraisal.asOpeningLoan
• Project.ProjectCashflow.Appraisal.asOtherCostsLine1
• Project.ProjectCashflow.Appraisal.asOtherCostsLine2
• Project.ProjectCashflow.Appraisal.asOtherDescription
• Project.ProjectCashflow.Appraisal.asOtherDescriptionLabel
• Project.ProjectCashflow.Appraisal.asOtherGrantLine1
• Project.ProjectCashflow.Appraisal.asOtherGrantLine2
• Project.ProjectCashflow.Appraisal.asPFSubsidyLine1
• Project.ProjectCashflow.Appraisal.asPFSubsidyLine2
• Project.ProjectCashflow.Appraisal.asPFSubsidyLine3
• Project.ProjectCashflow.Appraisal.asProjectID
• Project.ProjectCashflow.Appraisal.asPublicSubsidy
• Project.ProjectCashflow.Appraisal.asRCGF
• Project.ProjectCashflow.Appraisal.asRentedUnits
• Project.ProjectCashflow.Appraisal.asRentPeriods
• Project.ProjectCashflow.Appraisal.asRentToHomebuyUnits
• Project.ProjectCashflow.Appraisal.asResSales
• Project.ProjectCashflow.Appraisal.asResSalesUnits
• Project.ProjectCashflow.Appraisal.asSchemeCostIndex
• Project.ProjectCashflow.Appraisal.asSchemeType
• Project.ProjectCashflow.Appraisal.asTotalAcq
• Project.ProjectCashflow.Appraisal.asTotalFees
• Project.ProjectCashflow.Appraisal.asTotalWorks
• Project.ProjectCashflow.Appraisal.asTSC
• Project.ProjectCashflow.Appraisal.asVersion
• Project.ProjectCashflow.Appraisal.asWorksVAT
• Project.ProjectCashflow.Appraisal.asYield
• Project.ProjectCashflow.BudgetLog.blBalance
• Project.ProjectCashflow.BudgetLog.blBudgetExp
• Project.ProjectCashflow.BudgetLog.blBudgetInc
• Project.ProjectCashflow.BudgetLog.blBudgetST
• Project.ProjectCashflow.BudgetLog.blCashflowID
• Project.ProjectCashflow.BudgetLog.blFCExp
• Project.ProjectCashflow.BudgetLog.blFCInc
• Project.ProjectCashflow.BudgetLog.blFCST
• Project.ProjectCashflow.BudgetLog.blID
• Project.ProjectCashflow.BudgetLog.blInterest
• Project.ProjectCashflow.BudgetLog.blLEExp
• Project.ProjectCashflow.BudgetLog.blLEInc
• Project.ProjectCashflow.BudgetLog.blLEST
• Project.ProjectCashflow.BudgetLog.blNett
• Project.ProjectCashflow.BudgetLog.blTimeStamp
• Project.ProjectCashflow.BudgetLog.blTransExp
• Project.ProjectCashflow.BudgetLog.blTransInc
• Project.ProjectCashflow.BudgetLog.blTransST
• Project.ProjectCashflow.BudgetLog.blUserID
• Project.ProjectCashflow.CalculatedBalances_.cbpBalance
• Project.ProjectCashflow.CalculatedBalances_.cbpCashflowID
• Project.ProjectCashflow.CalculatedBalances_.cbpDate
• Project.ProjectCashflow.CalculatedBalances_.cbpID
• Project.ProjectCashflow.CalculatedBalances_.cbpInterest
• Project.ProjectCashflow.CalculatedBalances_.cbpNett
• Project.ProjectCashflow.CalculatedBalances_.cbtBalance
• Project.ProjectCashflow.CalculatedBalances_.cbtCashflowID
• Project.ProjectCashflow.CalculatedBalances_.cbtDate
• Project.ProjectCashflow.CalculatedBalances_.cbtID
• Project.ProjectCashflow.CalculatedBalances_.cbtInterest
• Project.ProjectCashflow.CalculatedBalances_.cbtNett
• Project.ProjectCashflow.CashflowAttributeValue.cavAttributeID
• Project.ProjectCashflow.CashflowAttributeValue.cavCashflowID
• Project.ProjectCashflow.CashflowAttributeValue.cavID
• Project.ProjectCashflow.CashflowAttributeValue.cavValue
• Project.ProjectCashflow.Note.DueDate
• Project.ProjectCashflow.Note.IsComplete
• Project.ProjectCashflow.Note.NoteID
• Project.ProjectCashflow.Note.NoteText
• Project.ProjectCashflow.Note.OriginatorID
• Project.ProjectCashflow.Note.OriginatorKey
• Project.ProjectCashflow.Order.orCashflowID
• Project.ProjectCashflow.Order.orDate
• Project.ProjectCashflow.Order.orDatePaid
• Project.ProjectCashflow.Order.orID
• Project.ProjectCashflow.Order.orOrgID
• Project.ProjectCashflow.Order.orProjectCostCodeID
• Project.ProjectCashflow.Order.orProjectID
• Project.ProjectCashflow.Order.orRef
• Project.ProjectCashflow.Order.orScope
• Project.ProjectCashflow.Order.orSupplyEnd
• Project.ProjectCashflow.Order.orSupplyStart
• Project.ProjectCashflow.Order.orValue
• Project.ProjectCashflow.ProjectCostCode.pccAccountCodeID
• Project.ProjectCashflow.ProjectCostCode.pccCashflowID
• Project.ProjectCashflow.ProjectCostCode.pccID
• Project.ProjectCashflow.SpreadProfile.spCashflowID
• Project.ProjectCashflow.SpreadProfile.spDelayStart
• Project.ProjectCashflow.SpreadProfile.spExtendFinish
• Project.ProjectCashflow.SpreadProfile.spFinishDate
• Project.ProjectCashflow.SpreadProfile.spFunds
• Project.ProjectCashflow.SpreadProfile.spID
• Project.ProjectCashflow.SpreadProfile.spOverwrite
• Project.ProjectCashflow.SpreadProfile.spProfile
• Project.ProjectCashflow.SpreadProfile.spProfileName
• Project.ProjectCashflow.SpreadProfile.spStartDate
• Project.ProjectCashflow.SpreadProfile.spUseExisting
• Project.ProjectCashflow.Transaction.tAccountCodeID
• Project.ProjectCashflow.Transaction.tAmount
• Project.ProjectCashflow.Transaction.tCashflowID
• Project.ProjectCashflow.Transaction.tDC
• Project.ProjectCashflow.Transaction.tDescription
• Project.ProjectCashflow.Transaction.tID
• Project.ProjectCashflow.Transaction.tPostedDate
• Project.ProjectCashflow.Transaction.tReference
• Project.ProjectCashflow.Transaction.tSchemeCode
• Project.ProjectCashflow.Transaction.tSupplierCode
• Project.ProjectCashflow.Transaction.tTransDate
• Project.ProjectCashflow.Transaction.tTransPeriod
• Project.ProjectOrganisation.Note.DueDate
• Project.ProjectOrganisation.Note.IsComplete
• Project.ProjectOrganisation.Note.NoteID
• Project.ProjectOrganisation.Note.NoteText
• Project.ProjectOrganisation.Note.OriginatorID
• Project.ProjectOrganisation.Note.OriginatorKey
• Project.ProjectOrganisation.OrganisationAdvance.oaID
• Project.ProjectOrganisation.OrganisationAdvance.oaOrgID
• Project.ProjectOrganisation.OrganisationAdvance.oaTopicID
• Project.ProjectOrganisation.OrganisationAdvance.oaValue
• Project.ProjectWorkflow.Task.tActualFinishDate
• Project.ProjectWorkflow.Task.tActualStartDate
• Project.ProjectWorkflow.Task.tCategoryID
• Project.ProjectWorkflow.Task.tComment
• Project.ProjectWorkflow.Task.tCurrentStatus
• Project.ProjectWorkflow.Task.tFinishAbstractID
• Project.ProjectWorkflow.Task.tFinishDate
• Project.ProjectWorkflow.Task.tForceComplete
• Project.ProjectWorkflow.Task.tID
• Project.ProjectWorkflow.Task.tOrder
• Project.ProjectWorkflow.Task.tOriginatorID
• Project.ProjectWorkflow.Task.tScope
• Project.ProjectWorkflow.Task.tSecurityCompleteID
• Project.ProjectWorkflow.Task.tSecurityLevelID
• Project.ProjectWorkflow.Task.tStartAbstractID
• Project.ProjectWorkflow.Task.tStartDate
• Project.ProjectWorkflow.Task.tTaskName
• Project.ProjectWorkflow.Task.tWorkflowID
• Project.Property.Note.DueDate
• Project.Property.Note.IsComplete
• Project.Property.Note.NoteID
• Project.Property.Note.NoteText
• Project.Property.Note.OriginatorID
• Project.Property.Note.OriginatorKey
• Project.Property.PropertyDetail.prdAttributeID
• Project.Property.PropertyDetail.prdID
• Project.Property.PropertyDetail.prdPropertyID
• Project.Property.PropertyDetail.prdValue
• Project.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocAccountCodeID
• Project.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocAmount
• Project.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocAppraisalDescriptionID
• Project.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocDate
• Project.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocID
• Project.Appraisal.AppraisalUnitDetail.Property.prAppraisalUnitID
• Project.Appraisal.AppraisalUnitDetail.Property.prCaption
• Project.Appraisal.AppraisalUnitDetail.Property.prID
• Project.Appraisal.AppraisalUnitDetail.Property.prIsParent
• Project.Appraisal.AppraisalUnitDetail.Property.prOrder
• Project.Appraisal.AppraisalUnitDetail.Property.prParentPropertyID
• Project.Appraisal.AppraisalUnitDetail.Property.prProjectID
• Project.ProjectCashflow.Allocation.AllocationDetail.adActual
• Project.ProjectCashflow.Allocation.AllocationDetail.adActualAmount
• Project.ProjectCashflow.Allocation.AllocationDetail.adActualUnits
• Project.ProjectCashflow.Allocation.AllocationDetail.adAllocationID
• Project.ProjectCashflow.Allocation.AllocationDetail.adForecast
• Project.ProjectCashflow.Allocation.AllocationDetail.adForecastAmount
• Project.ProjectCashflow.Allocation.AllocationDetail.adForecastUnits
• Project.ProjectCashflow.Allocation.AllocationDetail.adHcAmount
• Project.ProjectCashflow.Allocation.AllocationDetail.adHcTarget
• Project.ProjectCashflow.Allocation.AllocationDetail.adHcUnits
• Project.ProjectCashflow.Allocation.AllocationDetail.adID
• Project.ProjectCashflow.Appraisal.AppraisalDescription.adAppraisalID
• Project.ProjectCashflow.Appraisal.AppraisalDescription.adDescription
• Project.ProjectCashflow.Appraisal.AppraisalDescription.adID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audAppraisalID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audBeds
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audCatalystDevType
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audComRent
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audDwelling
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audEquityShare
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audExtended
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audFlat
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audFloorArea
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audGroundRent
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audHabRooms
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audHCProductType
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audHCTargetRent
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audJan99
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audNewBuild
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audOpenMarketValue
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audOtherCharges
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audOtherDescLabel
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audOtherDescValue
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audPersonalCharges
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audPersons
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audResRent
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audServiceCharges
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audShared
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audSheltered
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audStoreys
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audUnits
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.audUnitType
• Project.ProjectCashflow.Appraisal.Note.DueDate
• Project.ProjectCashflow.Appraisal.Note.IsComplete
• Project.ProjectCashflow.Appraisal.Note.NoteID
• Project.ProjectCashflow.Appraisal.Note.NoteText
• Project.ProjectCashflow.Appraisal.Note.OriginatorID
• Project.ProjectCashflow.Appraisal.Note.OriginatorKey
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclAccCodeID
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclBudgetChanged
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclID
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclLatestChanged
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclNewValue
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclOldValue
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclReason
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclTimeStamp
• Project.ProjectCashflow.ProjectCostCode.AccountCodeLog.aclUserID
• Project.ProjectCashflow.ProjectCostCode.Budget.bBudget
• Project.ProjectCashflow.ProjectCostCode.Budget.bCommitted
• Project.ProjectCashflow.ProjectCostCode.Budget.bCostCodeID
• Project.ProjectCashflow.ProjectCostCode.Budget.bID
• Project.ProjectCashflow.ProjectCostCode.Budget.bLatestEstimate
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoCostCodeID
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoCurveEndOffset
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoCurveStartOffset
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoEndDate
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoEveryNMonths
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoFirstRetDate
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoFirstRetPercentage
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoFundsOption
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoID
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoSecondRetDate
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoSecondRetPercentage
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoSpreadOption
• Project.ProjectCashflow.ProjectCostCode.CodeForecastOption.cfoStartDate
• Project.ProjectCashflow.ProjectCostCode.CodesToTask.cttCodeID
• Project.ProjectCashflow.ProjectCostCode.CodesToTask.cttCodePercentage
• Project.ProjectCashflow.ProjectCostCode.CodesToTask.cttID
• Project.ProjectCashflow.ProjectCostCode.CodesToTask.cttTaskID
• Project.ProjectCashflow.ProjectCostCode.CodesToTask.cttTaskPercentage
• Project.ProjectCashflow.ProjectCostCode.Forecast.fCostCodeID
• Project.ProjectCashflow.ProjectCostCode.Forecast.fDate
• Project.ProjectCashflow.ProjectCostCode.Forecast.fForecast
• Project.ProjectCashflow.ProjectCostCode.Forecast.fID
• Project.ProjectCashflow.ProjectCostCode.Order.orCashflowID
• Project.ProjectCashflow.ProjectCostCode.Order.orDate
• Project.ProjectCashflow.ProjectCostCode.Order.orDatePaid
• Project.ProjectCashflow.ProjectCostCode.Order.orID
• Project.ProjectCashflow.ProjectCostCode.Order.orOrgID
• Project.ProjectCashflow.ProjectCostCode.Order.orProjectCostCodeID
• Project.ProjectCashflow.ProjectCostCode.Order.orProjectID
• Project.ProjectCashflow.ProjectCostCode.Order.orRef
• Project.ProjectCashflow.ProjectCostCode.Order.orScope
• Project.ProjectCashflow.ProjectCostCode.Order.orSupplyEnd
• Project.ProjectCashflow.ProjectCostCode.Order.orSupplyStart
• Project.ProjectCashflow.ProjectCostCode.Order.orValue
• Project.ProjectWorkflow.Task.CodesToTask.cttCodeID
• Project.ProjectWorkflow.Task.CodesToTask.cttCodePercentage
• Project.ProjectWorkflow.Task.CodesToTask.cttID
• Project.ProjectWorkflow.Task.CodesToTask.cttTaskID
• Project.ProjectWorkflow.Task.CodesToTask.cttTaskPercentage
• Project.ProjectWorkflow.Task.Note.DueDate
• Project.ProjectWorkflow.Task.Note.IsComplete
• Project.ProjectWorkflow.Task.Note.NoteID
• Project.ProjectWorkflow.Task.Note.NoteText
• Project.ProjectWorkflow.Task.Note.OriginatorID
• Project.ProjectWorkflow.Task.Note.OriginatorKey
• Project.ProjectWorkflow.Task.ProjectDependency.pdConnectionType
• Project.ProjectWorkflow.Task.ProjectDependency.pdDependentTaskID
• Project.ProjectWorkflow.Task.ProjectDependency.pdDependsOnTaskID
• Project.ProjectWorkflow.Task.ProjectDependency.pdDurationType
• Project.ProjectWorkflow.Task.ProjectDependency.pdID
• Project.ProjectWorkflow.Task.ProjectDependency.pdOffset
• Project.ProjectWorkflow.Task.ProjectTaskLink.ptlID
• Project.ProjectWorkflow.Task.ProjectTaskLink.ptlLinkID
• Project.ProjectWorkflow.Task.ProjectTaskLink.ptlTaskID
• Project.ProjectWorkflow.Task.TaskCompletion.tcDate
• Project.ProjectWorkflow.Task.TaskCompletion.tcID
• Project.ProjectWorkflow.Task.TaskCompletion.tcNotes
• Project.ProjectWorkflow.Task.TaskCompletion.tcStatus
• Project.ProjectWorkflow.Task.TaskCompletion.tcTaskID
• Project.ProjectWorkflow.Task.TaskCompletion.tcUserID
• Project.Appraisal.AppraisalUnitDetail.Property.Note.DueDate
• Project.Appraisal.AppraisalUnitDetail.Property.Note.IsComplete
• Project.Appraisal.AppraisalUnitDetail.Property.Note.NoteID
• Project.Appraisal.AppraisalUnitDetail.Property.Note.NoteText
• Project.Appraisal.AppraisalUnitDetail.Property.Note.OriginatorID
• Project.Appraisal.AppraisalUnitDetail.Property.Note.OriginatorKey
• Project.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdAttributeID
• Project.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdID
• Project.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdPropertyID
• Project.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdValue
• Project.ProjectCashflow.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocAccountCodeID
• Project.ProjectCashflow.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocAmount
• Project.ProjectCashflow.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocAppraisalDescriptionID
• Project.ProjectCashflow.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocDate
• Project.ProjectCashflow.Appraisal.AppraisalDescription.AppraisalDetailedOnCost.adocID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.prAppraisalUnitID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.prCaption
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.prID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.prIsParent
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.prOrder
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.prParentPropertyID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.prProjectID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.Note.DueDate
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.Note.IsComplete
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.Note.NoteID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.Note.NoteText
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.Note.OriginatorID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.Note.OriginatorKey
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdAttributeID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdPropertyID
• Project.ProjectCashflow.Appraisal.AppraisalUnitDetail.Property.PropertyDetail.prdValue

Let me know if this is of use to you.