page302
390
FROM TABLE ( RESULT_SCAN ( last_query_id()))
WHERE "owner" NOT IN ( 'ACCOUNTADMIN', 'SECURITYADMIN', 'SYSADMIN')
ORDER BY 1 ASC;
Identifying procedures and functions is largely similar in form to the tables and
views shown earlier:
SHOW PROCEDURES;
SHOW FUNCTIONS;
As shown earlier, your objective is to extract a consistent form of metadata for each of
the
SHOW
commands for consistent later use. This SQL statement works for the previous
SHOW
commands noting the
object_type
should be changed according to the desired
object type. You can also filter out objects created without a schema; the
schema_name
is
empty string.
SELECT "catalog_name"||'.'||"schema_name"||'.'||"name" AS path_to_object,
'PROCEDURE | FUNCTION' AS object_type
FROM TABLE ( RESULT_SCAN ( last_query_id()))
WHERE "owner" NOT IN ( 'ACCOUNTADMIN', 'SECURITYADMIN', 'SYSADMIN')
AND "schema_name" != ''
ORDER BY 1 ASC;
You may prefer to wrap all the previous
SHOW
commands and extend subsequent SQL
statements to insert into tables. I suggest a stored procedure encapsulating logic into a
single container for periodic reuse.
Identifying Object Dependencies
Object dependencies are created when a parent object references a child object. For
example, a view is an example of a parent object with dependencies on those objects
referenced in the view declaration. In this manner, you can observe object dependencies
exist in hierarchical form, as shown in Figure 10-9.
ChapTer 10
OpTimizing perfOrmanCe