SQLGetSchema Function

Excel Developer Reference

It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLGetSchema and the other ODBC functions in the Xlodbc.xla add-in.

SQLGetSchema returns information about the structure of the data source on a particular connection.

This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor.

SQLGetSchema(ConnectionNum, TypeNum, QualifierText)

Value Meaning
1 A list of all the available data sources.
2 A list of databases on the current connection.
3 A list of owners in a database on the current connection.
4 A list of tables for a given owner and database on the current connection.
5 A list of columns in a particular table and their ODBC SQL data types, in a two-dimensional array. The first field contains the name of the column; the second field is the column’s ODBC SQL data type.
6 The user ID of the current user.
7 The name of the current database.
8 The name of the data source defined during setup or defined by using the ODBC Control Panel Administrator.
9 The name of the DBMS that the data source uses. For example, ORACLE or SQL Server.
10 The server name for the data source.
11 The terminology used by the data source to refer to the owners. For example "owner", "Authorization ID", or "Schema".
12 The terminology used by the data source to refer a table For example, "table" or "file".
13 The terminology used by the data source to refer to a qualifier. For example, "database" or "folder".
14 The terminology used by the data source to refer to a procedure. For example, "database procedure", "stored procedure", or "procedure".
TypeNum QualifierText
3 The name of the database in the current data source. SQLGetSchema returns the names of the table owners in that database.
4 Both a database name and an owner name. The syntax consists of the database name followed by the owner's name, with a period separating the two; for example, "DatabaseName.OwnerName". This function returns an array of table names that are located in the given database and owned by the given owner.
5 The name of a table. SQLGetSchema returns information about the columns in the table.

Return Value

The return value from a successful call to SQLGetSchema depends on the type of information that’s requested.

If SQLGetSchema cannot find the requested information, it returns Error 2042.

If ConnectionNum isn’t valid, this function returns Error 2015.

Remarks

SQLGetSchema uses the ODBC API functions SQLGetInfo and SQLTables to find the requested information.

Example

This example retrieves the database name and DBMS name for the Northwind database and then displays these names in a message box.

  databaseName = "Northwind"
chan = SQLOpen("DSN=" & databaseName)
dsName = SQLGetSchema(chan, 8)
dsDBMS = SQLGetSchema(chan, 9)
MsgBox "Database name is " & dsName & ", and its DBMS is " & dsDBMS
SQLClose chan