The Get Columns Methods

The Get Columns Methods

To list the names of the columns in a table in column number order, you can use either the GetAllColumns()Opens in a new tab or GetVisibleColumns()Opens in a new tab method, as follows:

  SET stat=##class(%SYSTEM.SQL.Schema).GetAllColumns("Sample.Person",.byname,.bynum)
  IF stat=1 {
    SET i=1
    WHILE $DATA(bynum(i)) { WRITE "name is ",bynum(i),"   col num is ",i,!
                            SET i=i+1 }
  }
  ELSE { WRITE "GetAllColumns() cannot locate specified table" }

GetAllColumns() lists all defined columns, including hidden columns. If a table references an embedded %SerialObject class, GetAllColumns() first lists all of the columns in the persistent class, including the property that references the %SerialObject, then lists all of the %SerialObject properties. This is shown in the following GetAllColumns() results:

name is ID   col num is 1
name is Age   col num is 2
name is Home   col num is 3
name is Name   col num is 4
name is x__classname   col num is 5
name is Home_City   col num is 6
name is Home_Phone   col num is 7
name is Home_Phone_AreaCode   col num is 8
name is Home_Phone_Country   col num is 9
name is Home_Phone_TNum   col num is 10
name is Home_PostalCode   col num is 11
name is Home_State   col num is 12
name is Home_Street   col num is 13

You can also use this method to determine the column number for a specified column name, as follows:

  SET stat=##class(%SYSTEM.SQL.Schema).GetAllColumns("Sample.Person",.byname)
  IF stat=1 {
         WRITE "Home_State is column number ",byname("Home_State"),!  }
  ELSE { WRITE "GetAllColumns() cannot locate specified table" }