sqlColumns: Query Column Structure in ODBC Tables

sqlColumnsR Documentation

Query Column Structure in ODBC Tables


Enquire about the column structure of tables on an ODBC database connection.


sqlColumns(channel, sqtable, errors = FALSE, as.is = TRUE,
           special = FALSE, catalog = NULL, schema = NULL,
           literal = FALSE)

sqlPrimaryKeys(channel, sqtable, errors = FALSE, as.is = TRUE,
               catalog = NULL, schema = NULL)



connection object as returned by odbcConnect.


character string: a database table (or view or similar) name accessible from the connected DSN. If wildcards are allowed (only for sqlColumns(special=FALSE)), results for all matching tables.


logical: if true halt and display error, else return -1.


see sqlGetResults.


logical. If true, return only the column(s) needed to specify a row uniquely. Depending on the database, there might be none.

catalog, schema

NULL or character: additional information on where to locate the table: see sqlTables for driver-specific details. Wildcards may be supported in schema for sqlColumns(special=FALSE).


logical: wildcards may be interpreted in schema and sqtable: if so this may suppress such interpretation.


The argument special = TRUE to sqlColumns returns the column(s) needed to specify a row uniquely. This is intended to form the basis of an SQL WHERE clause for update queries (see sqlUpdate), and what (if anything) it does is DBMS-specific. On many DBMSs it will return the primary keys if present: on others it will return a pseudo-column such as ‘⁠ROWID⁠’ (Oracle) or ‘⁠_ROWID_⁠’ (SQLite), either always (Oracle) or if there is no primary key.

Primary keys are implemented in some DBMSs and drivers. A table can have a single column designated as a primary key or, in some cases, multiple columns. Primary keys should not be nullable (that is, cannot contain missing values). They can be specified as part of a ‘⁠CREATE TABLE⁠’ statement or added by a ‘⁠ALTER TABLE⁠’ statement.

In principle specifying catalog should select an alternative database in MySQL or an attached database in SQLite, but neither works with current drivers.

If sqtable contains ‘⁠.⁠’ and neither catalog nor schema is supplied, an attempt is made to interpret qualifier.table as table table in schema qualifier (and for MySQL ‘schema’ means ‘database’, but the current drivers fail to interpret catalog=, so this does not yet work). (This can be suppressed by opening the connection with interpretDot = FALSE.) This has been tested successfully on PostgreSQL, SQL Server, Oracle, DB2 and Mimer.

Whether wildcards are accepted for sqtable and schema in sqlColumns(special = FALSE) depends on the driver and may be changed by the value of literal. For example, the PostgreSQL driver tested allowed wildcards in schema only if literal = FALSE and never in sqtable, whereas two MySQL drivers both failed to match a database when catalog was supplied and always allowed wildcards in sqtable even if literal = TRUE.


A data frame on success. If no data is returned, either a zero-row data frame or an error. (For example, if there are no primary keys or special column(s) in this table an empty data frame is returned, but if primary keys are not supported by the ODBC driver or DBMS, an error code results.)

The column names are not constant across ODBC versions so the data should be accessed by column number.

For sqlPrimaryKeys and sqlColumns(special=FALSE) the first four columns give the catalog, schema, table and column names (where applicable). For sqlPrimaryKeys the next two columns are the column sequence number (starting with 1) and name of the primary key: drivers can define further columns. For sqlColumns(special=FALSE) there are 18 columnns: see https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolumns-function?view=sql-server-ver15. Those beyond the first 6 shown in the examples give the ‘ordinal position’ (column 17) and further characteristics of the column type: see sqlTypeInfo.

For the numeric values returned by sqlColumns(special=TRUE) see https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlspecialcolumns-function?view=sql-server-ver15: the scope should always be 2 (the session) since that is the scope requested in the call. For the PSEUDO_COLUMN column, the possible values are 0 (unknown), 1 (no) and 2 (yes).


Michael Lapsley and Brian Ripley

See Also

odbcConnect, sqlQuery, sqlFetch, sqlSave, sqlTables, odbcGetInfo


## Not run: ## example results from MySQL
> channel <- odbcConnect("test")
> sqlDrop(channel, "USArrests", errors = FALSE) # precautionary
> sqlSave(channel, USArrests, addPK = TRUE)
> sqlColumns(channel, "USArrests")
1    ripley        <NA>  USArrests    rownames        12   varchar
2    ripley        <NA>  USArrests      Murder         8    double
3    ripley        <NA>  USArrests     Assault         4   integer
4    ripley        <NA>  USArrests    UrbanPop         4   integer
5    ripley        <NA>  USArrests        Rape         8    double
... 12 more columns

> sqlColumns(channel, "USArrests", special = TRUE)
1     2    rownames        12   varchar         255           255
1             NA             1

> sqlPrimaryKeys(channel, "USArrests")
1      <NA>        <NA>  USArrests    rownames       1 PRIMARY
> sqlDrop(channel, "USArrests")
> close(channel)

## End(Not run)

RODBC documentation built on Nov. 25, 2023, 5:07 p.m.