15.00 - The Default Database - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

The Default Database

The default database is a Teradata extension to SQL that defines a database that Teradata Database uses to look for unqualified names, such as table, view, trigger, or macro names, in SQL statements.

The default database is not the only database that Teradata Database uses to find an unqualified name in an SQL statement. Teradata Database also looks for the name in:

  • Other databases, if any, referenced by the SQL statement
  • The login user database for a volatile table, if the unqualified object name is a table name
  • The SYSLIB database, if the unqualified object name is a C or C++ UDF that is not in the default database
  • If the unqualified object name exists in more than one of the databases in which Teradata Database looks, the SQL statement produces an ambiguous name error.

    Establishing a Permanent Default Database

    You can establish a permanent default database that is invoked each time you log on.

     

    TO …

    USE one of the following SQL Data Definition statements...

    define a permanent default database

  • CREATE USER, with a DEFAULT DATABASE clause.
  • CREATE USER, with a PROFILE clause that specifies a profile that defines the default database.
  • change your permanent default database definition

  • MODIFY USER, with a DEFAULT DATABASE clause.
  • MODIFY USER, with a PROFILE clause.
  • MODIFY PROFILE, with a DEFAULT DATABASE clause.
  • add a default database when one had not been established previously

    For example, the following statement automatically establishes Personnel as the default database for Marks at the next logon:

    MODIFY USER marks AS 
    DEFAULT DATABASE = personnel ; 

    After you assign a default database, Teradata Database uses that database as one of the databases to look for all unqualified object references.

    To obtain information from a table, view, trigger, or macro in another database, fully qualify the table reference by specifying the database name, a FULLSTOP character, and the table name.

    Establishing a Default Database for a Session

    You can establish a default database for the current session that Teradata Database uses to look for unqualified object names in SQL statements.

     

    TO …

    USE …

    establish a default database for a session

    the DATABASE statement.

    For example, after entering the following SQL statement:

       DATABASE personnel ; 

    you can enter a SELECT statement:

       SELECT deptno (TITLE 'Org'), name 
       FROM employee ; 

    which has the same results as:

       SELECT deptno (TITLE 'Org'), name
       FROM personnel.employee;

    To establish a default database, you must have some privilege on an object in that database. Once defined, the default database remains in effect until the end of a session or until it is replaced by a subsequent DATABASE statement.

    Default Database for a Stored Procedure

    Stored procedures can contain SQL statements with unqualified object references. The default database that Teradata Database uses for the unqualified object references depends on whether the CREATE PROCEDURE statement includes the SQL SECURITY clause and, if it does, which option the SQL SECURITY clause specifies.

    For details on whether Teradata Database uses the creator, invoker, or owner of the stored procedure as the default database, see CREATE PROCEDURE in SQL Data Definition Language.

    Related Topics

     

    For more information on …

    See …

    the DATABASE statement

    SQL Data Definition Language.

    using the CREATE PROFILE statement to define the default database for member users

    the CREATE USER statement

    the MODIFY USER statement

    fully-qualified names

    “Referencing Object Names in a Request” on page 91.

    “Unqualified Object Names” on page 92.