Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

SET SCHEMA

Description

SET SCHEMA specifies a new default qualifier for database object names (database objects include tables, indexes, views, synonyms, procedures, and triggers).

When you connect to a database with a particular user name, that name becomes the default qualifier for database object names. This means you do not have to qualify references to tables, for instance, that were created under the same user name. However, you must qualify references to all other tables with the user name of the user who created them.

SET SCHEMA allows you to change the user name that FairCom DB SQL uses as the default qualifier for database object names. The name specified in SET SCHEMA becomes the new default qualifier for object names.

Note: SET SCHEMA does not change your user name or affect authentication. It only changes the default qualifier.

Syntax

SET SCHEMA ' qualifier_name ' ;

Arguments

' qualifier_name '

The new qualifier name is enclosed in single quotation marks.

Notes

  • SET SCHEMA does not check whether qualifier_name is a valid user name.
  • Metadata for objects created without an explicit qualifier will show qualifier_name as the owner.
  • SET SCHEMA does not start or end a transaction.

Examples

The following interactive SQL example shows changing the default qualifier through SET SCHEMA. The example:

  • Invokes ISQL as the user admin, the owner of the system catalog tables
  • Queries the systables catalog tables as admin
  • Uses SET SCHEMA to change the default qualifier to fred
  • Creates a table and queries systables to show that the newly-created table is owned by fred

ISQL> -- What is the user name for the current connection?

ISQL> select user() from syscalctable;

ADMIN

------

admin

1 record selected

ISQL> -- Show the name and owner of non-system tables:

ISQL> select tbl, owner from systables where tbltype <> 'S';

TBL OWNER

--- -----

t1 admin

test admin

test ctree

3 records selected

ISQL> set schema 'fred';

ISQL> create table freds_table (c1 int);

ISQL> create index freds_table_ix on freds_table (c1);

ISQL> select tbl, owner from systables where tbltype <> 'S';

select tbl, owner from systables where tbltype <> 'S';

*

error(-20005): Table/View/Synonym not found

ISQL> -- Oops! Must now qualify references to the admin-owned tables:

ISQL> select tbl, owner from admin.systables where tbltype <> 'S';

TBL OWNER

--- -----

t1 admin

test admin

test ctree

freds_table fred

4 records selected

Authorization

None.

SQL Compliance

SQL-92

Environment

Embedded SQL and interactive

Related Statements

None

TOCIndex