SQL Reference

TABLE_SCHEMA

>>-TABLE_SCHEMA---(--objectname----+------------------+--)-----><
                                   '-,--objectschema--'
 

The schema is SYSIBM.

The TABLE_SCHEMA function returns the schema name of the object found after any alias chains have been resolved. The specified objectname (and objectschema) 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.

objectname

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

objectschema

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

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

The data type of the result of the function is VARCHAR(128). If objectname can be null, the result can be null; if objectname is null, the result is the null value. If objectschema 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:

table

The value for objectname was either a table name (the input or default value of objectschema is returned) or an alias name that resolved to a table for which the schema name is returned.

view

The value for objectname was either a view name (the input or default value of objectschema is returned) or an alias name that resolved to a view for which the schema name is returned.

undefined object

The value for objectname was either an undefined object (the input or default value of objectschema is returned) or an alias name that resolved to an undefined object for which the schema name is returned.

Therefore, if a non-null objectname 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.

Examples:

• 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.

• 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.

• 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'.

• 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.

[ DB2 List of Books | Search the DB2 Books ]