Triggers, IDs, Dependencies, and 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. NULL when event is LOGIN. |
| "time" | SMALLINT | | Action time: 0 = BEFORE. 1 = AFTER |
| "orientation" | SMALLINT | | 0 = FOR EACH ROW, 1 = FOR EACH STATEMENT |
| "event" | SMALLINT | | Event type: 0 = INSERT, 1 = DELETE, 2 = UPDATE, 3 = TRUNCATE, 4 = LOGIN. |
| "old_name" | VARCHAR | | The correlation SQL name for the previous state. NULL if not set. |
| "new_name" | VARCHAR | | The correlation SQL name for the new state. NULL if not set. |
| "condition" | VARCHAR | | The WHEN clause condition. NULL if not set. |
| "statement" | VARCHAR | | The original create (or replace) trigger statement. Keywords and unquoted names are converted to lowercase. |
sys.ids
| name | type | references | description |
|---|
| "id" | INTEGER | sys.*.id | The identifier of the object. The referenced table where this object id is stored is available in column "sys_table". |
| "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.dependencies
| name | type | references | description |
|---|
| "id" | INTEGER | sys.ids.id | A row with "id" x and "depend_id" y means that y depends on x |
| "depend_id" | INTEGER | sys.ids.id | A row with "id" x and "depend_id" y means that y depends on x |
| "depend_type" | SMALLINT | sys.dependency_types.dependency_type_id | The type of the depender y |
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_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" forms a 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..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. |
| 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 convenient 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;