The default database is a Teradata extension to SQL that defines a database that is used to look for unqualified names, such as table, view, trigger, or macro names, in SQL statements.
- Other databases, if any, referenced by the SQL statement
- The log on 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 Vantage 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.
Goal | Data Definition Statements to Use |
---|---|
Define a permanent default database. |
|
Change your permanent default database definition. |
|
Add a default database when one was never established. |
|
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, Vantage uses that database as one of the databases to look for all unqualified object references.
To get 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 Vantage uses to look for unqualified object names in SQL statements.
To establish a default database for a session, use 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 a privilege on an object in that database. After definition, the default database remains in effect until the session ends or the default database is changed 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 Vantage uses for the unqualified object references depends on whether the CREATE PROCEDURE statement includes the SQL SECURITY clause and, if so, which option the SQL SECURITY clause specifies.
Related Information
- Fully-qualified names, see Referencing Object Names in a Request or Object Names.