SAS/ACCESS Software for Relational Databases: Reference

Special OLE DB Queries

The following special queries are supported by the SAS/ACCESS interface to OLE DB. Many databases provide or use system tables that enable queries to return the list of available tables, columns, procedures, and other useful information. In OLE DB, much of this functionality is provided through special APIs (application programming interfaces) in order to accommodate databases that do not follow the SQL table structure. You can use these special queries on non-SQL and on SQL databases. The general format of the special queries is:

OLEDB::schema-rowset("parameter 1","parameter n")
where

OLEDB::: is required to distinguish special queries from regular queries
schema-rowset: is the specific schema rowset that is being called. All valid schema rowsets are listed under the IDBSchemaRowset Interface in the Microsoft OLE DB Programmer's Reference. Both OLEDB:: and schema-rowset are case-sensitive.
"parameter n": is a quoted string that is enclosed by commas. The values for the special query arguments are specific to each data source. For example, you supply the fully qualified table name for the "Qualifier" argument. In dBase, the value of "Qualifier" might be c:\dbase\tst.dbf, and in SQL Server, the value might be test.customer. In addition, depending on the data source that you use, values for "Owner" might be a user ID, a database name, or a library. All arguments are optional. If you specify some but not all parameters within an argument, use a comma to indicate the omitted parameters. If you do not specify any parameters, commas are not necessary. Note that these special queries might not be available for all OLE DB providers.

The following special queries are supported:

OLEDB::ASSERTIONS( <"Catalog", "Schema", "Constraint-Name"> ): returns assertions defined in the catalog that are owned by a given user.
OLEDB::CATALOGS( <"Catalog"> ): returns physical attributes associated with catalogs that are accessible from the DBMS.
OLEDB::CHARACTER_SETS( <"Catalog", "Schema","Character-Set-Name">): returns the character sets defined in the catalog that are accessible to a given user.
OLEDB::CHECK_CONSTRAINTS(<"Catalog", "Schema", "Constraint-Name">): returns check constraints defined in the catalog that are owned by a given user.
OLEDB::COLLATIONS(<"Catalog", "Schema", "Collation-Name">): returns the character collations defined in the catalog that are accessible to a given user.
OLEDB::COLUMN_DOMAIN_USAGE( <"Catalog", "Schema", "Domain-Name", "Column-Name">): returns the columns defined in the catalog that are dependent on a domain defined in the catalog and owned by a given user.
OLEDB::COLUMN_PRIVILEGES( <"Catalog", "Schema", "Table-Name", "Column-Name", "Grantor", "Grantee">): returns the privileges on columns of tables defined in the catalog that are available to or granted by a given user
OLEDB::COLUMNS( <"Catalog", "Schema", "Table-Name", "Column-Name">): returns the columns of tables defined in the catalogs that are accessible to a given user.
OLEDB::CONSTRAINT_COLUMN_USAGE(<"Catalog", "Schema", "Table-Name", "Column-Name">): returns the columns used by referential constraints, unique constraints, check constraints, and assertions that are defined in the catalog and owned by a given user.
OLEDB::CONSTRAINT_TABLE_USAGE(<"Catalog", "Schema", "Table-Name">): returns the tables used by referential constraints, unique constraints, check constraints, and assertions that are defined in the catalog and owned by a given user.
OLEDB::FOREIGN_KEYS(<"Primary-Key-Catalog", "Primary-Key-Schema", "Primary-Key-Table-Name", "Foreign-Key-Catalog", "Foreign-Key-Schema", "Foreign-Key-Table-Name">): returns the foreign key columns defined in the catalog by a given user.
OLEDB::INDEXES( <"Catalog", "Schema", "Index-Name", "Type", "Table-Name">): returns the indexes defined in the catalog that are owned by a given user.
OLEDB::KEY_COLUMN_USAGE(<"Constraint-Catalog", "Constraint-Schema", "Constraint-Name", "Table-Catalog", "Table-Schema", "Table-Name", "Column-Name">): returns the columns defined in the catalog that are constrained as keys by a given user.
OLEDB::PRIMARY_KEYS(<"Catalog", "Schema", "Table-Name">): returns the primary key columns defined in the catalog by a given user.
OLEDB::PROCEDURE_COLUMNS(<"Catalog", "Schema", "Procedure-Name", "Column-Name">): returns information about the columns of rowsets returned by procedures.
OLEDB::PROCEDURE_PARAMETERS(<"Catalog", "Schema", "Procedure-Name", "Parameter-Name">): returns information about the parameters and return codes of the procedures.
OLEDB::PROCEDURES(<"Catalog", "Schema", "Procedure-Name", "Procedure-Type">): returns procedures defined in the catalog that are owned by a given user.
OLEDB::PROVIDER_INFO(): returns output that contains the following columns: PROVIDER_NAME, PROVIDER_DESCRIPTION, and PROVIDER_PROPERTIES. The PROVIDER_PROPERTIES column contains a list of all the properties that the provider supports. The properties are separated by a semicolon(;). See the example.
OLEDB::PROVIDER_TYPES(<"Data Type", "Best-Match">): returns information on the base data types supported by the data provider.
OLEDB::REFERENTIAL_CONSTRAINTS(<"Catalog", "Schema", "Constraint-Name">): returns the referential constraints defined in the catalog that are owned by a given user.
OLEDB::SCHEMATA(<"Catalog", "Schema", "Owner">): returns the schemas that are owned by a given user.
OLEDB::SQL_LANGUAGES(): returns the conformance levels, options and dialects supported by the SQL-implementation processing data that is defined in the catalog.
OLEDB::STATISTICS(<"Catalog", "Schema", "Table-Name">): returns the statistics defined in the catalog that are owned by a given user.
OLEDB::TABLE_CONSTRAINTS(<"Constraint-Catalog", "Constraint-Schema", "Constraint-Name", "Table-Catalog", "Table-Schema", "Table-Name", "Constraint-Type">): returns the table constraints defined in the catalog that are owned by a given user.
OLEDB::TABLE_PRIVILEGES(<"Catalog", "Schema", "Table-Name", "Grantor", "Grantee">): returns the privileges on tables defined in the catalog that are available to or granted by a given user.
OLEDB::TABLES(<"Catalog", "Schema", "Table-Name", "Table-Type">): returns the tables defined in the catalog that are available to or granted by a given user.
OLEDB::TRANSLATIONS(<"Catalog", "Schema", "Translation-Name">): returns the character translations defined in the catalog that are accessible to a given user.
OLEDB::USAGE_PRIVILEGES(<"Catalog", "Schema", "Object-Name", "Object-Type", "Grantor", "Grantee">): returns the USAGE privileges on objects defined in the catalog that are available to or granted by a given user.
OLEDB::VIEW_COLUMN_USAGE(<"Catalog", "Schema", "View-Name">): returns the columns on which viewed tables, defined in the catalog and owned by a given user, are dependent.
OLEDB::VIEW_TABLE_USAGE(<"Catalog", "Schema", "View-Name">): returns the tables on which viewed tables, defined in the catalog and owned by a given user, are dependent.
OLEDB::VIEWS(<"Catalog", "Schema", "Table-Name">): returns the viewed tables defined in the catalog that are accessible to a given user.

For a complete description of each rowset and the columns that are defined in each rowset, refer to the Microsoft OLE DB Programmer's Reference.

Examples

In this example, you retrieve a rowset that displays all of the tables that are accessed by the schema HRDEPT:

proc sql;      
  connect to oledb("User ID"=dbajorge Password=dbajorge99
     "Data Source"="oracle8_loc");
  select * from connection to oledb
       (OLEDB::TABLES(,"HRDEPT"));

This next example uses the special query OLEDB::PROVIDER_INFO() to produce the output that follows it:

proc sql; 
  connect to oledb("User ID"=dbajorge Password=dbajorge99
     "Data Source"="oracle8_loc");
  select * from connection to oledb
       (OLEDB::PROVIDER_INFO());

Provider and Properties Output

PROVIDER_NAME PROVIDER_DESCRIPTION PROVIDER_PROPERTIES ------------- -------------------- ------------------- MSDAORA Microsoft OLE DB Password;User ID;Data Provider for Oracle Source;Window Handle;Locale Identifier;OLE DB Services; Prompt; Extended Properties; SampProv Microsoft OLE DB Data Source;Window Handle; Sample Provider Prompt;

You could then reference the output when automating a connection to the provider. For the previous result set, you could write the following SAS/ACCESS LIBNAME statement:

libname mydblib oledb provider=msdaora
   props=('Data Source'=OraServer 'User ID'=Smith);

Chapter Contents
Previous
Next
Top of Page