dbo.fSQLColumns

dbo.fSQLColumns

This function returns the list of column names in a specified table.

dbo.fSQLColumns <'database_qualifier' | null>, <'table_name' | null>, <'column_name' | null>)

Table 96

dbo.fSQLColumns Arguments

database_qualifier

Current database

Name of the database from which the details are to be obtained.

(no default value)

Name of the table whose column information is required

All columns for the specified table

Column name of the table specified.

Returned Result Set

Table 97

dbo.fSQLColumns Returned Result Set

TABLE_QUALIFIER

Name of the database; NULL if not applicable to the database.

Schema name of the table; NULL if not applicable to the database.

VARCHAR not NULL

Name of the table

Column name of the table or an empty string for a column that does not have a name.

SMALLINT not NULL

SQL data type of the column. See PSQL Transactional and Relational Data Types.

Name of the data type of the column corresponding to DATA_TYPE value

The precision of the column if the data type is Decimal, Numeric, and so forth. See Precision and Scale of Decimal Data Types.

If DATA_TYPE is CHAR or VARCHAR, this column contains the maximum length in characters of the column. For datetime data types, this is the total number of characters required to display the value when it is converted to characters. For numeric data types, this is either the total number of digits or the total number of bits allowed in the column, according to the RADIX column.

The length in bytes of data transferred on an SQLGetData, SQLFetch, or SQLFetchScroll operation if SQL_C_DEFAULT is specified.

For numeric data, this size may differ from the size of the data stored in the database. This value might differ from COLUMN_SIZE column for character data.

The total number of significant digits to the right of the decimal point. For TIME and TIMESTAMP, this column contains the number of digits in the fractional seconds component.

For the other data types, this is the decimal digits of the column in the database. See Precision and Scale of Decimal Data Types.

Base for numeric data types

For numeric data types, either 10 or 2.

•

10—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of decimal digits allowed for the column.

•

2—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of bits allowed in the column.

NULL is returned for data types where RADIX is not applicable.

Indicates whether the procedure column accepts a NULL value:

•

0 = NO_NULLS—the procedure column does not accept NULL values.

•

1 = NULLABLE—the procedure column accepts NULL values.

•

2 = NULLABLE_UNKNOWN—it is not known if the procedure column accepts NULL values.

The default value of the column.

If NULL was specified as the default value, this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, this column is NULL.

SMALLINT not NULL

Value of the SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor. This column is the same as the TYPE_NAME column, except for data types DATE, TIME, DATETIME, and TIMESTAMP.

For those data types, the SQL_DATA_TYPE field in the result set returns the following: SQL_DATE for DATE, SQL_TIME for TIME, and SQL_TIMESTAMP for DATETIME and TIMESTAMP.

SQL_DATETIME_SUB

Subtype code for DATE, TIME, DATETIME, and TIMESTAMP. For other data types, this column returns a NULL.

•

1 = for DATE (SQL_CODE_DATE)

•

2 = for TIME (SQL_CODE_TIME)

•

3 = for DATETIME and TIMESTAMP (SQL_CODE_TIMESTAMP)

CHAR_OCTET_LENGTH

Maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL.

ORDINAL_POSITION

INTEGER not NULL

For input and output parameters, the ordinal position of the parameter in the procedure definition (in increasing parameter order, starting at 1).

For a return value (if any), 0 is returned. For result-set columns, the ordinal position of the column in the result set, with the first column in the result set being number 1.

"NO" if the column does not include NULLs.

"YES" if the column includes NULLs.

This column returns a zero-length string if nullability is unknown. The value returned for this column differs from the value returned for the NULLABLE column.

This example returns information for all columns in the Room table in the default Demodata sample database.

SELECT * FROM dbo.fSQLColumns ('Demodata', 'room', null)

Result Set (abbreviated for space considerations):

COLUMN_NAME DATA_TYPE LENGTH ORDINAL_POSITION

============== ========= ====== ================

Building_Name 1 25 1

4 rows were affected.