AppLib DO FOR ENTIRE SCHEMA

From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Parameters 4 Example 5 Source Code

Syntax

CALL APPLIB.DO_FOR_ENTIRE_SCHEMA( sqlString, schemaName, objTypeStr )

Purpose

Executes a SQL statement for all tables, views, or tables and views, in a schema. The statement must be either DDL or DML (it cannot be a query since no results are returned).

Parameters

• sqlString: SQL statement to be executed. Will be executed for each object, with wildcard %TABLE_NAME% replaced with the name of the table or view in question. It will be replaced with an identifier and schema quoted identifier string. ie, %TABLE_NAME% ==> "SCOTT"."emp"[VARCHAR(65535)]
• schemaName: name of schema [VARCHAR(255)]
• objTypeStr: sets which object type(s) to execute for. Legal values are "TABLES", "VIEWS" and "TABLES_AND_VIEWS".

Example

CALL APPLIB.DO_FOR_ENTIRE_SCHEMA( 'grant select on %TABLE_NAME% to MONDRIAN_USER', 'TESTSCHEMA', 'VIEWS' )

Equivalent to running APPLIB.GRANT_SELECT_FOR_SCHEMA('TESTSCHEMA', 'MONDRIAN_USER'), but only affecting views and not tables.

(doesn't return anything)

Source Code

http://p4web.eigenbase.org/open/dev/luciddb/src/com/lucidera/luciddb/applib/util/DoForEntireSchemaUdp.java