dbo.fSQLColumns
This function returns the list of column names in a specified table.
Syntax
dbo.fSQLColumns <'
database_qualifier
' | null>, <'
table_name
' | null>, <'
column_name
' | null>)
Arguments
Table 110
dbo.fSQLColumns Arguments
Parameter
Type
Default Value
Description
database_qualifier
VARCHAR
Current database
Name of the database from which the details are to be obtained.
table_name
VARCHAR
(no default value)
Name of the table whose column information is required
column_name
VARCHAR
All columns for the specified table
Column name of the table specified.
Returned Result Set
Table 111
dbo.fSQLColumns Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR
Name of the database; NULL if not applicable to the database.
TABLE_OWNER
VARCHAR
Schema name of the table; NULL if not applicable to the database.
TABLE_NAME
VARCHAR not NULL
Name of the table
COLUMN_NAME
VARCHAR
Column name of the table or an empty string for a column that does not have a name.
DATA_TYPE
SMALLINT not NULL
ODBC data type code of the column. See
Pervasive PSQL Data Types and Equivalent ODBC Data Types
.
TYPE_NAME
VARCHAR
Name of the data type of the column corresponding to DATA_TYPE value
PRECISION
INTEGER
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 SQL_CHAR or SQL_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.
LENGTH
INTEGER
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.
SCALE
SMALLINT
The total number of significant digits to the right of the decimal point. For SQL_TYPE_TIME and SQL_TYPE_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
.
RADIX
SMALLINT
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.
NULLABLE
SMALLINT
Indicates whether the procedure column accepts a NULL value:
•
0 = SQL_NO_NULLS—the procedure column does not accept NULL values.
•
1 = SQL_NULLABLE—the procedure column accepts NULL values.
•
2 = SQL_NULLABLE_UNKNOWN—it is not known if the procedure column accepts NULL values.
REMARKS
VARCHAR
Remarks field
COLUMN_DEF
VARCHAR
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.
SQL_DATA_TYPE
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
SMALLINT
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
INTEGER
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.
IS_NULLABLE
VARCHAR
"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.
Example
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
Number 4 4 2
Capacity 5 2 3
Type 1 20 4
4 rows were affected.