Skip to main content

Triggers, ids, dependencies, comments

sys.triggers
name type references description
"id" INTEGER   The unique internal trigger identifier.
"name" VARCHAR   Its SQL name.
"table_id" INTEGER sys.tables.id The corresponding triggering table identifier
"time" SMALLINT  

BEFORE/AFTER

"orientation" SMALLINT   FOR EACH ROW/FOR EACH STATEMENT
"event" SMALLINT   Update/insert/delete event.
"old_name" VARCHAR   The correlation SQL name for the previous state.
"new_name" VARCHAR   The correlation SQL name for the new state.
"condition" VARCHAR   The SQL condition.
"statement" VARCHAR   A SQL statement to be executed.

 

sys.ids
name type references description
"id" INTEGER sys.*.id The identifier of the object.
"name" VARCHAR   The name of the object.
"schema_id" INTEGER sys.schemas.id If applicable: The schema identifier the object belongs to, else NULL.
"table_id" INTEGER sys.tables.id If applicable: The table identifier the object belongs to, else NULL.
"table_name" VARCHAR sys.tables.name If applicable: The table name the object belongs to, else NULL.
"obj_type" VARCHAR   Object type name.
"sys_table" VARCHAR   Corresponding system table name where details on the object can be queried.
sys.dependency_types
name type references description
"dependency_type_id" SMALLINT   The unique identifier for the dependency type.
"dependency_type_name" VARCHAR   The unique name of this dependency type.
sys.dependencies
name type references description
"id" INTEGER sys.ids.id The identifier which has a dependency.
"depend_id" INTEGER sys.ids.id The identifier reference on which it depends. The combination of "id" and "depend_id" is unique key.
"depend_type" SMALLINT sys.dependency_types
.dependency_type_id
To which sys.<obj>.id it depends: a table or view, a column, a function, an auth, an object.
See sys.dependency_types for meaning of the identifiers
sys.dependencies_vw
name type references description
"id" INTEGER sys.ids.id The identifier of the object which has a dependency.
"obj_type" VARCHAR   Object type name.
"name" VARCHAR   The name of the object.
"used_by_id" INTEGER sys.ids.id The identifier reference on which it depends. The combination of "id" and "used_by_id" is unique key.
"used_by_obj_type" VARCHAR   Object type name of the used_by_id.
"used_by_name" VARCHAR   The name of the object referenced by the used_by_id.
"depend_type" SMALLINT sys.dependency_types
.dependency_type_id
To which sys.<obj>.id it depends: a table or view, a column, a function, an auth, an object.
"dependency_type_name" VARCHAR sys.dependency_types
.dependency_type_name
The name of the dependency type.

 

sys.comments
name type references description
"id" INTEGER sys.ids.id The identifier of the object which has a comment.
"remark" VARCHAR   The remark comment associated to the object.

 

Following convenience views exist to find specific dependencies more easily:

SELECT * FROM sys.dependency_owners_on_schemas;
SELECT * FROM sys.dependency_schemas_on_users;
SELECT * FROM sys.dependency_tables_on_foreignkeys;
SELECT * FROM sys.dependency_tables_on_functions;
SELECT * FROM sys.dependency_tables_on_indexes;
SELECT * FROM sys.dependency_tables_on_procedures;
SELECT * FROM sys.dependency_tables_on_triggers;
SELECT * FROM sys.dependency_tables_on_views;
SELECT * FROM sys.dependency_views_on_functions;
SELECT * FROM sys.dependency_views_on_procedures;
SELECT * FROM sys.dependency_views_on_views;
SELECT * FROM sys.dependency_columns_on_functions;
SELECT * FROM sys.dependency_columns_on_indexes;
SELECT * FROM sys.dependency_columns_on_keys;
SELECT * FROM sys.dependency_columns_on_procedures;
SELECT * FROM sys.dependency_columns_on_triggers;
SELECT * FROM sys.dependency_columns_on_types;
SELECT * FROM sys.dependency_columns_on_views;
SELECT * FROM sys.dependency_keys_on_foreignkeys;
SELECT * FROM sys.dependency_functions_on_functions;
SELECT * FROM sys.dependency_functions_on_procedures;
SELECT * FROM sys.dependency_functions_on_triggers;
SELECT * FROM sys.dependency_functions_on_types;
SELECT * FROM sys.dependency_functions_on_views;
SELECT * FROM sys.dependency_args_on_types;