COLUMNS Command

You can use QueryPad’s COLUMNS command to display information about the columns in a table or in a particular set of tables. When you use the COLUMNS command, QueryPad displays a list of the columns that match the criteria in your command. QueryPad can only provide columns information for those tables that are on the system to which QueryPad is currently connected.

To use the COLUMNS command:

1. Display QueryPad
2. Connect to the appropriate system (see Connect QueryPad to a Database)
3. Enter the ?COLUMNS command followed by the arguments that are appropriate to your requirements.
4. The correct syntax is:

5. ?COLUMNS <Schema> <Table> <Column>

6. Where:
   • <Schema> is the group that contains the tables that have the columns you want QueryPad to display. Do not include the angle brackets < >.
   • <Table> is the name of the table that contains the columns that you want QueryPad to display.
   • <Column> is the name of the column you want QueryPad to display.

     If you specify an exact schema and table, then only the columns of that table are listed. However, if you specify a pattern for a schema or table (by using the % character), then the columns that match the pattern are listed.

     Example:

     ?COLUMNS % % %

     This will list the columns in every table (as the % represents every schema, every table, and every column respectively)

     

     ?COLUMNS % CDBObject %

     This will list every column in the CDBObject table.

     

     ?COLUMNS % CPointAlgManual Current%

     This will list every column that is in the Internal Analog table and has a name that begins with ‘Current’.

     

     ?COLUMNS % % CurrentState

     This will list the CurrentState columns of every table.

     

   • Press the CTRL and the Enter keys simultaneously to submit your command.
     QueryPad displays a list of the columns that match the criteria set in your command.

For each column in the list, QueryPad displays:

• Row—The number of the row occupied by the column in QueryPad’s result set (column list). This row number is for the results displayed in QueryPad and is included to make it easier to read the QueryPad results. It does not relate to the rows in the database.
• Name—The name of the column.
• Datatype—The type of the data in the column. This can be:
  • 2 (VT_I2) - Signed 2-byte integer
  • 3 (VT_I4) - Signed 4-byte integer (also used for reference and color columns)
  • 4 (VT_R4) - Single precision floating point
  • 5 (VT_R8) - Double precision floating point
  • 7 (VT_DATE) - Date/time
  • 8 (VT_BSTR) - String
  • 11 (VT_BOOL) - Boolean
  • 12 (VT_VARIANT) - Column can contain data of any of the other types
  • 17 (VT_UI1) - Unsigned 1-byte integer
  • 18 (VT_UI2) - Unsigned 2-byte integer
  • 18 (VT_UI4) - Unsigned 4-byte integer
• Length—For columns that contain strings, the Length indicates the maximum number of characters permitted. For other types of column, the Length is 1. The string length only applies when data is returned via an external interface such as ODBC or OLE DB (if the string exceeds the maximum length, it will be truncated). If you use a Query Processor interface such as QueryPad, a Queries List or a Query in Logic, the full string will be displayed (the string is not truncated if it exceeds the maximum length).
• Table_Name—The name of the table to which the column belongs.
• Desc—A description of the column.
• Table Owner—The schema to which the table that contains the column belongs.
• QP_Attr—The column’s attributes. The attributes are a bitmask, which means that a number of the attributes can be combined by adding the relevant individual values. For example, if the QP_Attr is 12 (4+8), it means that the column in the ConfigVersion field and it is read-only.
• The possible attributes are:
  • 1 - The column is an index column (may or may not be unique).
  • 2 - The column is a unique index column (will be combined with 1).
  • 4 - The column is the ConfigVersion field.
  • 8 - The column is read-only.
  • 16 - The column is the Object ID.
  • 32 - The column specifies the foreground color.
  • 64 - The column specifies the blink.
  • 128 - The column is the item's short name.
  • 256 - The column is the item's full name.
  • 512 - The column specifies the background color.
  • 1024 - Not used.
  • 2048 - The column is a historic record ID.
  • 4096 - The column is the Event Journal's sequence number.
  • 8192 - The column is the Event Journal's comment number. This is used in conjunction with the sequence number to control Event Journal comments.
  • 16384 - The column specifies a hyperlink.
  • 32768 - The column is the Event Journal’s Source column.
  • 65536 - The column is an Area Of Interest field.
  • 131072 - The column belongs to an alarm condition.
  • 262144 - The column can be used as a constraint column.
• Ordinal—A number that identifies the position of the column in its table. The ordinal number indicates the order in which the columns are defined in your query (as the ?COLUMNS command returns the columns sorted in alphabetical order).