Additional Table-Valued Parameter Metadata
To retrieve metadata for a table-valued parameter, an application calls SQLProcedureColumns. For a table-valued parameter, SQLProcedureColumns returns a single row. Two additional SQL Server-specific columns, SS_TYPE_CATALOG_NAME and SS_TYPE_SCHEMA_NAME, have been added to provide schema and catalog information for table types associated with table-valued parameters. In conformance with the ODBC specification, SS_TYPE_CATALOG_NAME and SS_TYPE_SCHEMA_NAME appear before all driver-specific columns added in earlier versions of SQL Server, and after all columns mandated by ODBC itself.
The following table lists columns that are significant for table-valued parameters.
| Column name | Data type | Value/comments |
|---|---|---|
| DATA_TYPE | Smallint not NULL | SQL_SS_TABLE |
| TYPE_NAME | WVarchar(128) not NULL | The type name of the table-valued parameter. |
| COLUMN_SIZE | Integer | NULL |
| BUFFER_LENGTH | Integer | 0 |
| DECIMAL_DIGITS | Smallint | NULL |
| NUM_PREC_RADIX | Smallint | NULL |
| NULLABLE | Smallint not NULL | SQL_NULLABLE |
| REMARKS | Varchar | NULL |
| COLUMN_DEF | WVarchar(4000) | NULL |
| SQL_DATA_TYPE | Smallint not NULL | SQL_SS_TABLE |
| SQL_DATETIME_SUB | Smallint | NULL |
| CHAR_OCTET_LENGTH | Integer | NULL |
| ORDINAL_POSITION | Integer not NULL | The ordinal position of the parameter. |
| IS_NULLABLE | Varchar | "YES" |
| SS_TYPE_CATALOG_NAME | WVarchar(128) not NULL | The catalog that contains the type definition for the table type of the table-valued parameter. |
| SS_TYPE_SCHEMA_NAME | WVarchar(128) not NULL | The schema that contains the type definition for the table type of the table-valued parameter. |
The WVarchar columns are defined as Varchar in the ODBC specification, but are actually returned as WVarchar in all recent SQL Server ODBC drivers. This change was made when Unicide support was added to the ODBC 3.5 specification, but not called out explicitly.
To obtain additional metadata for table-valued parameters, an application uses the catalog functions SQLColumns and SQLPrimaryKeys. Before these functions are called for table-valued parameters, the application must set the statement attribute SQL_SOPT_SS_NAME_SCOPE to SQL_SS_NAME_SCOPE_TABLE_TYPE. This value indicates that the application requires metadata for a table type rather than an actual table. The application then passes the TYPE_NAME of the table-valued parameter as the TableName parameter. SS_TYPE_CATALOG_NAME and SS_TYPE_SCHEMA_NAME are used with the CatalogName and SchemaName parameters, respectively, to identify the catalog and schema for the table-valued parameter. When an application has finished retrieving metadata for table-valued parameters, it must set SQL_SOPT_SS_NAME_SCOPE back to its default value of SQL_SS_NAME_SCOPE_TABLE.
When SQL_SOPT_SS_NAME_SCOPE is set to SQL_SS_NAME_SCOPE_TABLE, queries to linked servers fail. Calls to SQLColumns or SQLPrimaryKeys with a catalog that contains a server component will fail.