dbo.tables (Tables and Views) - Dataedo repository 7

Dataedo repository 7

- Dataedo repository 7
  - Modules
  - Tables

Documentation	Dataedo repository 7
Schema	dbo
Name	tables
Title	Tables and Views
Module	Schema and Metadata
Type	Schema
Status	Active

Database tables and views.

Tables and views are distinguished by object_type column with 'TABLE' and 'VIEW' values.

Columns

		Name	Data type	Attributes	References	Description	Type	Status
	1	table_id	int	Identity	dbo.tables (Tables and Views)	Row id	Metadata	Active
Row id Identity References: dbo.tables (Tables and Views) Type: Metadata Status: Active
	2	database_id	int			Documentation which object is part of. Ref: databases.	Metadata	Active
Documentation which object is part of. Ref: databases. Type: Metadata Status: Active
	3	schema	nvarchar(250)			DBMS schema name	Schema	Active
DBMS schema name Type: Schema Status: Active
	4	name	nvarchar(250)			Object name	Schema	Active
Object name Type: Schema Status: Active
	5	object_type	nvarchar(100)			Database object type. 'TABLE' for tables, 'VIEW' for views.	Schema	Active
Database object type. 'TABLE' for tables, 'VIEW' for views. Type: Schema Status: Active
	6	subtype	nvarchar(100)			For tables: TABLE - regular table, default EXTERNAL_TABLE- External table FOREIGN_TABLE - Foreign table FILE_TABLE - File table GRAPH_TABLE - Graph table GRAPH_NODE_TABLE - Graph node/vertex table GRAPH_EDGE_TABLE - Graph edge table TEMPORAL_TABLE - Temporal table SYSTEM_VERSIONED_TABLE - System-versioned table HISTORY_TABLE - History table SEARCH_INDEX - Search index (as in Elasticsearch) COLLECTION - Collection (as in MongoDB) ENTITY - Entity CUBE - Cube DIMENSION - Dimension FLAT_FILE - Flat file OBJECT - Object STANDARD_OBJECT - Standard object CUSTOM_OBJECT - Custom object EXTERNAL_OBJECT - External object For views: VIEW - regular view, default MATERIALIZED_VIEW - Materialized view EDITIONING_VIEW - Editioning view INDEXED_VIEW - Indexed view NAMED_QUERY - Named query	Schema	Active
For tables: TABLE - regular table, default EXTERNAL_TABLE- External table FOREIGN_TABLE - Foreign table FILE_TABLE - File table GRAPH_TABLE - Graph table GRAPH_NODE_TABLE - Graph node/vertex table GRAPH_EDGE_TABLE - Graph edge table TEMPORAL_TABLE - Temporal table SYSTEM_VERSIONED_TABLE - System-versioned table HISTORY_TABLE - History table SEARCH_INDEX - Search index (as in Elasticsearch) COLLECTION - Collection (as in MongoDB) ENTITY - Entity CUBE - Cube DIMENSION - Dimension FLAT_FILE - Flat file OBJECT - Object STANDARD_OBJECT - Standard object CUSTOM_OBJECT - Custom object EXTERNAL_OBJECT - External object For views: VIEW - regular view, default MATERIALIZED_VIEW - Materialized view EDITIONING_VIEW - Editioning view INDEXED_VIEW - Indexed view NAMED_QUERY - Named query Type: Schema Status: Active
	7	source	nvarchar(50)	Default: N'DBMS'		Source of the row: 'DBMS' for imported from database, 'USER' for manual defined by user in Dataedo repository	Metadata	Active
Source of the row: 'DBMS' for imported from database, 'USER' for manual defined by user in Dataedo repository Default: N'DBMS' Type: Metadata Status: Active
	8	status	char(1)	Default: 'A'		'D' if table/view was deleted from DBMS, 'A' otherwise	Metadata	Active
'D' if table/view was deleted from DBMS, 'A' otherwise Default: 'A' Type: Metadata Status: Active
	9	definition	nvarchar(MAX)			View script imported from DBMS	Schema	Active
View script imported from DBMS Type: Schema Status: Active
	10	title	nvarchar(250)			Object title/alias in Dataedo repository	Documentation	Active
Object title/alias in Dataedo repository Type: Documentation Status: Active
	11	description	nvarchar(MAX)			Table/view description defined with Dataedo	Documentation	Active
Table/view description defined with Dataedo Type: Documentation Status: Active
	12	description_plain	nvarchar(MAX)			Holds description in plain text only, withouth HTML tags	Documentation	Active
Holds description in plain text only, withouth HTML tags Type: Documentation Status: Active
	13	description_search	nvarchar(MAX)			Holds description in plain text and lower case. Column used for searching.	Documentation	Active
Holds description in plain text and lower case. Column used for searching. Type: Documentation Status: Active
	14	dbms_creation_date	datetime			Time table/view was created in DBMS.	Metadata	Active
Time table/view was created in DBMS. Type: Metadata Status: Active
	15	dbms_last_modification_date	datetime			Time table/view was last updated in DBMS.	Metadata	Active
Time table/view was last updated in DBMS. Type: Metadata Status: Active
	16	synchronization_date	datetime			Time of last object update/import from DBMS.	Metadata	Active
Time of last object update/import from DBMS. Type: Metadata Status: Active
	17	synchronized_by	nvarchar(100)			Login of user that last updated/imported the object from DBMS.	Metadata	Active
Login of user that last updated/imported the object from DBMS. Type: Metadata Status: Active
	18	field1	nvarchar(MAX)			Custom field 1	Documentation	Active
Custom field 1 Type: Documentation Status: Active
	19	field2	nvarchar(MAX)			Custom field 2	Documentation	Active
Custom field 2 Type: Documentation Status: Active
	20	field3	nvarchar(MAX)			Custom field 3	Documentation	Active
Custom field 3 Type: Documentation Status: Active
	21	field4	nvarchar(MAX)			Custom field 4	Documentation	Active
Custom field 4 Type: Documentation Status: Active
	22	field5	nvarchar(MAX)			Custom field 5	Documentation	Active
Custom field 5 Type: Documentation Status: Active
	23	field6	nvarchar(MAX)			Custom field 6	Documentation	Active
Custom field 6 Type: Documentation Status: Active
	24	field7	nvarchar(MAX)			Custom field 7	Documentation	Active
Custom field 7 Type: Documentation Status: Active
	25	field8	nvarchar(MAX)			Custom field 8	Documentation	Active
Custom field 8 Type: Documentation Status: Active
	26	field9	nvarchar(MAX)			Custom field 9	Documentation	Active
Custom field 9 Type: Documentation Status: Active
	27	field10	nvarchar(MAX)			Custom field 10	Documentation	Active
Custom field 10 Type: Documentation Status: Active
	28	field11	nvarchar(MAX)			Custom field 11	Documentation	Active
Custom field 11 Type: Documentation Status: Active
	29	field12	nvarchar(MAX)			Custom field 12	Documentation	Active
Custom field 12 Type: Documentation Status: Active
	30	field13	nvarchar(MAX)			Custom field 13	Documentation	Active
Custom field 13 Type: Documentation Status: Active
	31	field14	nvarchar(MAX)			Custom field 14	Documentation	Active
Custom field 14 Type: Documentation Status: Active
	32	field15	nvarchar(MAX)			Custom field 15	Documentation	Active
Custom field 15 Type: Documentation Status: Active
	33	field16	nvarchar(MAX)			Custom field 16	Documentation	Active
Custom field 16 Type: Documentation Status: Active
	34	field17	nvarchar(MAX)			Custom field 17	Documentation	Active
Custom field 17 Type: Documentation Status: Active
	35	field18	nvarchar(MAX)			Custom field 18	Documentation	Active
Custom field 18 Type: Documentation Status: Active
	36	field19	nvarchar(MAX)			Custom field 19	Documentation	Active
Custom field 19 Type: Documentation Status: Active
	37	field20	nvarchar(MAX)			Custom field 20	Documentation	Active
Custom field 20 Type: Documentation Status: Active
	38	field21	nvarchar(MAX)			Custom field 21	Documentation	Active
Custom field 21 Type: Documentation Status: Active
	39	field22	nvarchar(MAX)			Custom field 22	Documentation	Active
Custom field 22 Type: Documentation Status: Active
	40	field23	nvarchar(MAX)			Custom field 23	Documentation	Active
Custom field 23 Type: Documentation Status: Active
	41	field24	nvarchar(MAX)			Custom field 24	Documentation	Active
Custom field 24 Type: Documentation Status: Active
	42	field25	nvarchar(MAX)			Custom field 25	Documentation	Active
Custom field 25 Type: Documentation Status: Active
	43	field26	nvarchar(MAX)			Custom field 26	Documentation	Active
Custom field 26 Type: Documentation Status: Active
	44	field27	nvarchar(MAX)			Custom field 27	Documentation	Active
Custom field 27 Type: Documentation Status: Active
	45	field28	nvarchar(MAX)			Custom field 28	Documentation	Active
Custom field 28 Type: Documentation Status: Active
	46	field29	nvarchar(MAX)			Custom field 29	Documentation	Active
Custom field 29 Type: Documentation Status: Active
	47	field30	nvarchar(MAX)			Custom field 30	Documentation	Active
Custom field 30 Type: Documentation Status: Active
	48	field31	nvarchar(MAX)			Custom field 31	Documentation	Active
Custom field 31 Type: Documentation Status: Active
	49	field32	nvarchar(MAX)			Custom field 32	Documentation	Active
Custom field 32 Type: Documentation Status: Active
	50	field33	nvarchar(MAX)			Custom field 33	Documentation	Active
Custom field 33 Type: Documentation Status: Active
	51	field34	nvarchar(MAX)			Custom field 34	Documentation	Active
Custom field 34 Type: Documentation Status: Active
	52	field35	nvarchar(MAX)			Custom field 35	Documentation	Active
Custom field 35 Type: Documentation Status: Active
	53	field36	nvarchar(MAX)			Custom field 36	Documentation	Active
Custom field 36 Type: Documentation Status: Active
	54	field37	nvarchar(MAX)			Custom field 37	Documentation	Active
Custom field 37 Type: Documentation Status: Active
	55	field38	nvarchar(MAX)			Custom field 38	Documentation	Active
Custom field 38 Type: Documentation Status: Active
	56	field39	nvarchar(MAX)			Custom field 39	Documentation	Active
Custom field 39 Type: Documentation Status: Active
	57	field40	nvarchar(MAX)			Custom field 40	Documentation	Active
Custom field 40 Type: Documentation Status: Active
	58	creation_date	datetime	Default: getdate()		Standard row creation timestamp	Metadata	Active
Standard row creation timestamp Default: getdate() Type: Metadata Status: Active
	59	created_by	nvarchar(1024)	Default: suser_sname()		Dataedo username that created row	Metadata	Active
Dataedo username that created row Default: suser_sname() Type: Metadata Status: Active
	60	last_modification_date	datetime	Default: getdate()		Standard row last update timestamp	Metadata	Active
Standard row last update timestamp Default: getdate() Type: Metadata Status: Active
	61	modified_by	nvarchar(1024)	Default: suser_sname()		Dataedo username that last updated row	Metadata	Active
Dataedo username that last updated row Default: suser_sname() Type: Metadata Status: Active
	62	source_id	int			For internal use only	Internal	Active
For internal use only Type: Internal Status: Active
	63	temp_sync_status	bit	Default: 0		For internal use only	Internal	Active
For internal use only Default: 0 Type: Internal Status: Active
	64	exists_in_DBMS	bit			For internal use only	Internal	Active
For internal use only Type: Internal Status: Active
	65	update_id	int		dbo.schema_updates (Imports/Updates)	Id of the last schema import/update from source database where element was created/modified	Metadata	Active
Id of the last schema import/update from source database where element was created/modified References: dbo.schema_updates (Imports/Updates) Type: Metadata Status: Active

Relations

Foreign table	Primary table	Join	Title / Name / Description
dbo.tables (Tables and Views)	dbo.schema_updates (Imports/Updates)	dbo.tables.update_id = dbo.schema_updates.update_id	fk_tables_schema_updates
dbo.tables.update_id = dbo.schema_updates.update_id Name: fk_tables_schema_updates
dbo.tables (Tables and Views)	dbo.tables (Tables and Views)	dbo.tables.table_id = dbo.tables.table_id	FK_tables_tables
dbo.tables.table_id = dbo.tables.table_id Name: FK_tables_tables
dbo.tables (Tables and Views)	dbo.tables (Tables and Views)	dbo.tables.table_id = dbo.tables.table_id	FK_tables_tables
dbo.tables.table_id = dbo.tables.table_id Name: FK_tables_tables
dbo.columns (Columns)	dbo.tables (Tables and Views)	dbo.columns.table_id = dbo.tables.table_id	FK_column_table
dbo.columns.table_id = dbo.tables.table_id Name: FK_column_table
dbo.columns_changes	dbo.tables (Tables and Views)	dbo.columns_changes.table_id = dbo.tables.table_id	fk_columns_changes_tables
dbo.columns_changes.table_id = dbo.tables.table_id Name: fk_columns_changes_tables
dbo.erd_nodes (ERD Nodes)	dbo.tables (Tables and Views)	dbo.erd_nodes.table_id = dbo.tables.table_id	FK_erd_nodes_tables
dbo.erd_nodes.table_id = dbo.tables.table_id Name: FK_erd_nodes_tables
dbo.tables_changes	dbo.tables (Tables and Views)	dbo.tables_changes.table_id = dbo.tables.table_id	fk_tables_changes_tables
dbo.tables_changes.table_id = dbo.tables.table_id Name: fk_tables_changes_tables
dbo.tables_modules (Tables-modules)	dbo.tables (Tables and Views)	dbo.tables_modules.table_id = dbo.tables.table_id	FK_tables_modules_tables
dbo.tables_modules.table_id = dbo.tables.table_id Name: FK_tables_modules_tables
dbo.tables_relations (Table relationships)	dbo.tables (Tables and Views)	dbo.tables_relations.fk_table_id = dbo.tables.table_id	Foreign table FK_tables_relations_table_fk
dbo.tables_relations.fk_table_id = dbo.tables.table_id Title: Foreign table Name: FK_tables_relations_table_fk
dbo.tables_relations (Table relationships)	dbo.tables (Tables and Views)	dbo.tables_relations.pk_table_id = dbo.tables.table_id	Primary table FK_tables_relations_table_pk
dbo.tables_relations.pk_table_id = dbo.tables.table_id Title: Primary table Name: FK_tables_relations_table_pk
dbo.tables_relations_changes	dbo.tables (Tables and Views)	dbo.tables_relations_changes.pk_table_id = dbo.tables.table_id	fk_tables_relations_changes_tables
dbo.tables_relations_changes.pk_table_id = dbo.tables.table_id Name: fk_tables_relations_changes_tables
dbo.tables_relations_changes	dbo.tables (Tables and Views)	dbo.tables_relations_changes.fk_table_id = dbo.tables.table_id	fk_tables_relations_changes_tables
dbo.tables_relations_changes.fk_table_id = dbo.tables.table_id Name: fk_tables_relations_changes_tables
dbo.triggers (Triggers)	dbo.tables (Tables and Views)	dbo.triggers.table_id = dbo.tables.table_id	FK_triggers_table
dbo.triggers.table_id = dbo.tables.table_id Name: FK_triggers_table
dbo.triggers_changes	dbo.tables (Tables and Views)	dbo.triggers_changes.table_id = dbo.tables.table_id	fk_triggers_changes_tables
dbo.triggers_changes.table_id = dbo.tables.table_id Name: fk_triggers_changes_tables
dbo.unique_constraints (Unique keys)	dbo.tables (Tables and Views)	dbo.unique_constraints.table_id = dbo.tables.table_id	FK_unique_constraints_tables
dbo.unique_constraints.table_id = dbo.tables.table_id Name: FK_unique_constraints_tables
dbo.unique_constraints_changes	dbo.tables (Tables and Views)	dbo.unique_constraints_changes.table_id = dbo.tables.table_id	fk_unique_constraints_changes_tables
dbo.unique_constraints_changes.table_id = dbo.tables.table_id Name: fk_unique_constraints_changes_tables

Unique keys

	Key name	Columns	Description
	PK_tables	table_id	Table primary key
Table primary key

Triggers

	Key name	When	Description
	trg_tables_change_track_insert	After Insert	Schema change tracking trigger
-- ============================================= -- Description: Insert table's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_change_track_insert] ON [tables] FOR INSERT AS -- skip manual objects created through Dataedo application -- or first documentation import to Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [INSERTED] WHERE [source] = 'USER' ) OR EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] JOIN [schema_updates] [u] ON [u].[update_id] = [i].[update_id] AND [u].[type] = 'IMPORT' ) BEGIN RETURN; END; INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [dbms_last_modification_date], [operation], [valid_from], [update_id] ) SELECT [i].[table_id], [i].[database_id], [i].[schema], [i].[name], [i].[object_type], [i].[subtype], [i].[definition], [i].[dbms_last_modification_date], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i];
Schema change tracking trigger
-- ============================================= -- Description: Insert table's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_change_track_insert] ON [tables] FOR INSERT AS -- skip manual objects created through Dataedo application -- or first documentation import to Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [INSERTED] WHERE [source] = 'USER' ) OR EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] JOIN [schema_updates] [u] ON [u].[update_id] = [i].[update_id] AND [u].[type] = 'IMPORT' ) BEGIN RETURN; END; INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [dbms_last_modification_date], [operation], [valid_from], [update_id] ) SELECT [i].[table_id], [i].[database_id], [i].[schema], [i].[name], [i].[object_type], [i].[subtype], [i].[definition], [i].[dbms_last_modification_date], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i];
	trg_tables_change_track_update	After Update	Schema change tracking trigger
-- ============================================= -- Description: Insert table's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_change_track_update] ON [tables] FOR UPDATE AS -- skip manual objects created through Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [INSERTED] WHERE [source] = 'USER' ) BEGIN RETURN; END; -- check if object property change IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] WHERE ISNULL([d].[schema],'') <> [i].[schema] OR ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[object_type],'') <> [i].[object_type] OR ISNULL([d].[subtype],'') <> [i].[subtype] OR ISNULL([d].[definition],'') <> [i].[definition] OR ISNULL([d].[dbms_last_modification_date],'') <> [i].[dbms_last_modification_date] ) BEGIN UPDATE [tables_changes] SET [valid_to] = GETDATE() FROM [tables_changes] [c] INNER JOIN [inserted] [u] ON [c].[table_id] = [u].[table_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for table - when table was updated INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [before_schema], [before_name], [before_object_type], [before_subtype], [before_definition], [dbms_last_modification_date], [operation], [valid_from], [update_id] ) SELECT [i].[table_id], [i].[database_id], [i].[schema], [i].[name], [i].[object_type], [i].[subtype], [i].[definition], [d].[schema], [d].[name], [d].[object_type], [d].[subtype], [d].[definition], [i].[dbms_last_modification_date], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [tables_changes] [c] WHERE [c].[table_id] = [d].[table_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' -- and isnull(d.last_modification_date,'') <> i.last_modification_date AND (ISNULL([d].[schema],'') <> [i].[schema] OR ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[object_type],'') <> [i].[object_type] OR ISNULL([d].[subtype],'') <> [i].[subtype] OR ISNULL([d].[definition],'') <> [i].[definition] OR ISNULL([d].[dbms_last_modification_date],'') <> [i].[dbms_last_modification_date]); END; BEGIN -- insert changes (deleted values) for table - when table was deleted INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [dbms_last_modification_date], [operation], [valid_to], [update_id] ) SELECT [d].[table_id], [d].[database_id], [d].[schema], [d].[name], [d].[object_type], [d].[subtype], [d].[definition], [i].[dbms_last_modification_date], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for table - when table was restored INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [dbms_last_modification_date], [operation], [valid_from], [update_id] ) SELECT [i].[table_id], [i].[database_id], [i].[schema], [i].[name], [i].[object_type], [i].[subtype], [i].[definition], [i].[dbms_last_modification_date], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
Schema change tracking trigger
-- ============================================= -- Description: Insert table's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_change_track_update] ON [tables] FOR UPDATE AS -- skip manual objects created through Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [INSERTED] WHERE [source] = 'USER' ) BEGIN RETURN; END; -- check if object property change IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] WHERE ISNULL([d].[schema],'') <> [i].[schema] OR ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[object_type],'') <> [i].[object_type] OR ISNULL([d].[subtype],'') <> [i].[subtype] OR ISNULL([d].[definition],'') <> [i].[definition] OR ISNULL([d].[dbms_last_modification_date],'') <> [i].[dbms_last_modification_date] ) BEGIN UPDATE [tables_changes] SET [valid_to] = GETDATE() FROM [tables_changes] [c] INNER JOIN [inserted] [u] ON [c].[table_id] = [u].[table_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for table - when table was updated INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [before_schema], [before_name], [before_object_type], [before_subtype], [before_definition], [dbms_last_modification_date], [operation], [valid_from], [update_id] ) SELECT [i].[table_id], [i].[database_id], [i].[schema], [i].[name], [i].[object_type], [i].[subtype], [i].[definition], [d].[schema], [d].[name], [d].[object_type], [d].[subtype], [d].[definition], [i].[dbms_last_modification_date], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [tables_changes] [c] WHERE [c].[table_id] = [d].[table_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' -- and isnull(d.last_modification_date,'') <> i.last_modification_date AND (ISNULL([d].[schema],'') <> [i].[schema] OR ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[object_type],'') <> [i].[object_type] OR ISNULL([d].[subtype],'') <> [i].[subtype] OR ISNULL([d].[definition],'') <> [i].[definition] OR ISNULL([d].[dbms_last_modification_date],'') <> [i].[dbms_last_modification_date]); END; BEGIN -- insert changes (deleted values) for table - when table was deleted INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [dbms_last_modification_date], [operation], [valid_to], [update_id] ) SELECT [d].[table_id], [d].[database_id], [d].[schema], [d].[name], [d].[object_type], [d].[subtype], [d].[definition], [i].[dbms_last_modification_date], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for table - when table was restored INSERT INTO [tables_changes] ([table_id], [database_id], [schema], [name], [object_type], [subtype], [definition], [dbms_last_modification_date], [operation], [valid_from], [update_id] ) SELECT [i].[table_id], [i].[database_id], [i].[schema], [i].[name], [i].[object_type], [i].[subtype], [i].[definition], [i].[dbms_last_modification_date], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_id] = [d].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
	trg_tables_Modify	After Insert, Update	Standard insert/update trigger setting last_modification_date, modified by columns
-- ============================================= -- Author: Piotr Kononow -- Create date: 2014-06-18 -- Description: Updates last_modification_date, modified_by columns on insert or update -- ============================================= CREATE TRIGGER [trg_tables_Modify] ON dbo.tables AFTER INSERT, UPDATE AS BEGIN UPDATE [tables] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE table_id IN (SELECT DISTINCT table_id FROM Inserted) END
Standard insert/update trigger setting last_modification_date, modified by columns
-- ============================================= -- Author: Piotr Kononow -- Create date: 2014-06-18 -- Description: Updates last_modification_date, modified_by columns on insert or update -- ============================================= CREATE TRIGGER [trg_tables_Modify] ON dbo.tables AFTER INSERT, UPDATE AS BEGIN UPDATE [tables] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE table_id IN (SELECT DISTINCT table_id FROM Inserted) END

Uses

Name
dbo.tables (Tables and Views)
dbo.schema_updates (Imports/Updates)
dbo.tables (Tables and Views)
dbo.trg_tables_change_track_insert
dbo.schema_updates (Imports/Updates)
dbo.tables_changes
dbo.trg_tables_change_track_update
dbo.tables_changes
dbo.trg_tables_Modify
dbo.tables (Tables and Views)

Used by

Name
dbo.tables (Tables and Views)
dbo.trg_columns_change_track_insert
dbo.trg_columns_change_track_update
dbo.trg_tables_Modify
dbo.trg_tables_relations_change_track_insert
dbo.trg_tables_relations_change_track_update
dbo.trg_tables_relations_cols_change_track_update
dbo.trg_triggers_change_track_insert
dbo.trg_triggers_change_track_update
dbo.trg_unique_constraints_change_track_insert
dbo.trg_unique_constraints_change_track_update
dbo.trg_unique_constraints_cols_change_track_update
dbo.columns (Columns)
dbo.columns_changes
dbo.erd_nodes (ERD Nodes)
dbo.tables (Tables and Views)
dbo.tables_changes
dbo.tables_modules (Tables-modules)
dbo.tables_relations (Table relationships) (Foreign table)
dbo.tables_relations (Table relationships) (Primary table)
dbo.tables_relations_changes
dbo.tables_relations_changes
dbo.triggers (Triggers)
dbo.triggers_changes
dbo.unique_constraints (Unique keys)
dbo.unique_constraints_changes

Exported: 2018-12-06 15:15, Last imported: 2018-11-21 15:11