TABLE_SCHEMA scalar function

The TABLE_SCHEMA function returns the schema name of the object found after any alias chains have been resolved.

The schema is SYSIBM.

The specified object-name (and object-schema) are used as the starting point of the resolution. If the starting point does not refer to an alias, the schema name of the starting point is returned. The resulting schema name may be of a table, view, or undefined object. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed.

object-name

A character expression representing the unqualified name (usually of an existing alias) to be resolved. object-name must have a data type of CHAR or VARCHAR and a length greater than 0 and less than 129 bytes.

object-schema

A character expression representing the schema used to qualify the supplied object-name value before resolution. object-schema must have a data type of CHAR or VARCHAR and a length greater than 0 and less than 129 bytes.

If object-schema is not supplied, the default schema is used for the qualifier.

The data type of the result of the function is VARCHAR(128 OCTETS). If object-name can be null, the result can be null; if object-name is null, the result is the null value. If object-schema is the null value, the default schema name is used. The result is the character string representing a schema name. The result schema could represent the schema name for one of the following objects:

table: The value for object-name was either a table name (the input or default value of object-schema is returned) or an alias name that resolved to a table for which the schema name is returned.
view: The value for object-name was either a view name (the input or default value of object-schema is returned) or an alias name that resolved to a view for which the schema name is returned.
undefined object: The value for object-name was either an undefined object (the input or default value of object-schema is returned) or an alias name that resolved to an undefined object for which the schema name is returned.

Therefore, if a non-null object-name value is given to this function, a value is always returned, even if the object name with the result schema name does not exist. For example, TABLE_SCHEMA('DEPT', 'PEOPLE') returns 'PEOPLE ' if the catalog entry is not found.

Notes

To improve performance in partitioned database configurations by avoiding the unnecessary communication that occurs between the coordinator partition and catalog partition when using the TABLE_SCHEMA and TABLE_NAME scalar functions, the BASE_TABLE table function can be used instead.

Examples

Example 1: PBIRD tries to select the statistics for a given table from SYSCAT.TABLES using an alias PBIRD.A1 defined on the table HEDGES.T1.
```
   SELECT NPAGES, CARD FROM SYSCAT.TABLES
     WHERE TABNAME = TABLE_NAME ('A1')
     AND TABSCHEMA = TABLE_SCHEMA ('A1')
```
The requested statistics for HEDGES.T1 are retrieved from the catalog.
Example 2: Select the statistics for an object called HEDGES.X1 from SYSCAT.TABLES using HEDGES.X1. Use TABLE_NAME and TABLE_SCHEMA since it is not known whether HEDGES.X1 is an alias or a table.
```
   SELECT NPAGES, CARD FROM SYSCAT.TABLES
     WHERE TABNAME = TABLE_NAME ('X1','HEDGES')
     AND TABSCHEMA = TABLE_SCHEMA ('X1','HEDGES')
```
Assuming that HEDGES.X1 is a table, the requested statistics for HEDGES.X1 are retrieved from the catalog.
Example 3: Select the statistics for a given table from SYSCAT.TABLES using an alias PBIRD.A2 defined on HEDGES.T2 where HEDGES.T2 does not exist.
```
   SELECT NPAGES, CARD FROM SYSCAT.TABLES
     WHERE TABNAME = TABLE_NAME ('A2','PBIRD')
     AND TABSCHEMA = TABLE_SCHEMA ('A2',PBIRD')
```
The statement returns 0 records as no matching entry is found in SYSCAT.TABLES where TABNAME = 'T2' and TABSCHEMA = 'HEDGES'.
Example 4: Select the qualified name of each entry in SYSCAT.TABLES along with the final referenced name for any alias entry.
```
   SELECT TABSCHEMA AS SCHEMA,  TABNAME AS NAME,
     TABLE_SCHEMA (BASE_TABNAME, BASE_TABSCHEMA) AS REAL_SCHEMA,
     TABLE_NAME (BASE_TABNAME, BASE_TABSCHEMA) AS REAL_NAME
     FROM SYSCAT.TABLES
```
The statement returns the qualified name for each object in the catalog and the final referenced name (after alias has been resolved) for any alias entries. For all non-alias entries, BASE_TABNAME and BASE_TABSCHEMA are null so the REAL_SCHEMA and REAL_NAME columns will contain nulls.