dbo.columns (Columns) - Dataedo repository 7

Dataedo repository 7

- Dataedo repository 7
  - Modules
  - Tables

Documentation	Dataedo repository 7
Schema	dbo
Name	columns
Title	Columns
Module	Schema and Metadata
Type	Schema
Status	Active

Holds tables' and views' columns.

Columns

		Name	Data type	Attributes	References	Description	Type	Status
	1	column_id	int	Identity		Row id	Metadata	Active
Row id Identity Type: Metadata Status: Active
	2	table_id	int		dbo.tables (Tables and Views)	Table to which column belongs	Metadata	Active
Table to which column belongs References: dbo.tables (Tables and Views) Type: Metadata Status: Active
	3	name	nvarchar(250)			Column name	Schema	Active
Column name Type: Schema Status: Active
	4	ordinal_position	int			Position of column in table in DBMS	Schema	Active
Position of column in table in DBMS Type: Schema Status: Active
	5	sort	int	Default: 99999		Alternative column sorting defined by user in Dataedo	Documentation	Active
Alternative column sorting defined by user in Dataedo Default: 99999 Type: Documentation Status: Active
	6	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
	7	status	char(1)	Default: 'A'		'D' if column was deleted in DBMS, 'A' otherwise. Deleted columns aren't exported to documentation.	Metadata	Active
'D' if column was deleted in DBMS, 'A' otherwise. Deleted columns aren't exported to documentation. Default: 'A' Type: Metadata Status: Active
	8	title	nvarchar(250)			Column title/alias in Dataedo repository	Documentation	Active
Column title/alias in Dataedo repository Type: Documentation Status: Active
	9	description	nvarchar(MAX)			Column description in Dataedo. Description is imported from database at first import and during schema update, but only if it was empty	Documentation	Active
Column description in Dataedo. Description is imported from database at first import and during schema update, but only if it was empty Type: Documentation Status: Active
	10	datatype	nvarchar(250)			Column data type	Schema	Active
Column data type Type: Schema Status: Active
	11	data_length	nvarchar(50)			Data length/precision as text. Depending on data type it can have differenent formats, e.g. "100", "5,4", "MAX" etc. Null if parameter has no length/precision defined	Schema	Active
Data length/precision as text. Depending on data type it can have differenent formats, e.g. "100", "5,4", "MAX" etc. Null if parameter has no length/precision defined Type: Schema Status: Active
	12	nullable	bit	Default: 1		1 if column is nullable, 0 otherwise	Schema	Active
1 if column is nullable, 0 otherwise Default: 1 Type: Schema Status: Active
	13	default_value	nvarchar(MAX)			Formula of column defalut value. NULL if field has not default value specification	Schema	Active
Formula of column defalut value. NULL if field has not default value specification Type: Schema Status: Active
	14	is_identity	bit	Default: 0		Is column an identity/auto incremented column. 1 - identity/auto increment, 0 - otherwise	Schema	Active
Is column an identity/auto incremented column. 1 - identity/auto increment, 0 - otherwise Default: 0 Type: Schema Status: Active
	15	is_computed	bit	Default: 0		Is column a computed column. 1 - computed column, 0 - regular column. If column is computed, you should expect that calculation formula is imported to computed_formula field	Schema	Active
Is column a computed column. 1 - computed column, 0 - regular column. If column is computed, you should expect that calculation formula is imported to computed_formula field Default: 0 Type: Schema Status: Active
	16	computed_formula	nvarchar(MAX)			Calculation formula for computed columns. NULL for regular fields	Schema	Active
Calculation formula for computed columns. NULL for regular fields Type: Schema Status: Active
	17	field1	nvarchar(MAX)			Custom field 1	Documentation	Active
Custom field 1 Type: Documentation Status: Active
	18	field2	nvarchar(MAX)			Custom field 2	Documentation	Active
Custom field 2 Type: Documentation Status: Active
	19	field3	nvarchar(MAX)			Custom field 3	Documentation	Active
Custom field 3 Type: Documentation Status: Active
	20	field4	nvarchar(MAX)			Custom field 4	Documentation	Active
Custom field 4 Type: Documentation Status: Active
	21	field5	nvarchar(MAX)			Custom field 5	Documentation	Active
Custom field 5 Type: Documentation Status: Active
	22	field6	nvarchar(MAX)			Custom field 6	Documentation	Active
Custom field 6 Type: Documentation Status: Active
	23	field7	nvarchar(MAX)			Custom field 7	Documentation	Active
Custom field 7 Type: Documentation Status: Active
	24	field8	nvarchar(MAX)			Custom field 8	Documentation	Active
Custom field 8 Type: Documentation Status: Active
	25	field9	nvarchar(MAX)			Custom field 9	Documentation	Active
Custom field 9 Type: Documentation Status: Active
	26	field10	nvarchar(MAX)			Custom field 10	Documentation	Active
Custom field 10 Type: Documentation Status: Active
	27	field11	nvarchar(MAX)			Custom field 11	Documentation	Active
Custom field 11 Type: Documentation Status: Active
	28	field12	nvarchar(MAX)			Custom field 12	Documentation	Active
Custom field 12 Type: Documentation Status: Active
	29	field13	nvarchar(MAX)			Custom field 13	Documentation	Active
Custom field 13 Type: Documentation Status: Active
	30	field14	nvarchar(MAX)			Custom field 14	Documentation	Active
Custom field 14 Type: Documentation Status: Active
	31	field15	nvarchar(MAX)			Custom field 15	Documentation	Active
Custom field 15 Type: Documentation Status: Active
	32	field16	nvarchar(MAX)			Custom field 16	Documentation	Active
Custom field 16 Type: Documentation Status: Active
	33	field17	nvarchar(MAX)			Custom field 17	Documentation	Active
Custom field 17 Type: Documentation Status: Active
	34	field18	nvarchar(MAX)			Custom field 18	Documentation	Active
Custom field 18 Type: Documentation Status: Active
	35	field19	nvarchar(MAX)			Custom field 19	Documentation	Active
Custom field 19 Type: Documentation Status: Active
	36	field20	nvarchar(MAX)			Custom field 20	Documentation	Active
Custom field 20 Type: Documentation Status: Active
	37	field21	nvarchar(MAX)			Custom field 21	Documentation	Active
Custom field 21 Type: Documentation Status: Active
	38	field22	nvarchar(MAX)			Custom field 22	Documentation	Active
Custom field 22 Type: Documentation Status: Active
	39	field23	nvarchar(MAX)			Custom field 23	Documentation	Active
Custom field 23 Type: Documentation Status: Active
	40	field24	nvarchar(MAX)			Custom field 24	Documentation	Active
Custom field 24 Type: Documentation Status: Active
	41	field25	nvarchar(MAX)			Custom field 25	Documentation	Active
Custom field 25 Type: Documentation Status: Active
	42	field26	nvarchar(MAX)			Custom field 26	Documentation	Active
Custom field 26 Type: Documentation Status: Active
	43	field27	nvarchar(MAX)			Custom field 27	Documentation	Active
Custom field 27 Type: Documentation Status: Active
	44	field28	nvarchar(MAX)			Custom field 28	Documentation	Active
Custom field 28 Type: Documentation Status: Active
	45	field29	nvarchar(MAX)			Custom field 29	Documentation	Active
Custom field 29 Type: Documentation Status: Active
	46	field30	nvarchar(MAX)			Custom field 30	Documentation	Active
Custom field 30 Type: Documentation Status: Active
	47	field31	nvarchar(MAX)			Custom field 31	Documentation	Active
Custom field 31 Type: Documentation Status: Active
	48	field32	nvarchar(MAX)			Custom field 32	Documentation	Active
Custom field 32 Type: Documentation Status: Active
	49	field33	nvarchar(MAX)			Custom field 33	Documentation	Active
Custom field 33 Type: Documentation Status: Active
	50	field34	nvarchar(MAX)			Custom field 34	Documentation	Active
Custom field 34 Type: Documentation Status: Active
	51	field35	nvarchar(MAX)			Custom field 35	Documentation	Active
Custom field 35 Type: Documentation Status: Active
	52	field36	nvarchar(MAX)			Custom field 36	Documentation	Active
Custom field 36 Type: Documentation Status: Active
	53	field37	nvarchar(MAX)			Custom field 37	Documentation	Active
Custom field 37 Type: Documentation Status: Active
	54	field38	nvarchar(MAX)			Custom field 38	Documentation	Active
Custom field 38 Type: Documentation Status: Active
	55	field39	nvarchar(MAX)			Custom field 39	Documentation	Active
Custom field 39 Type: Documentation Status: Active
	56	field40	nvarchar(MAX)			Custom field 40	Documentation	Active
Custom field 40 Type: Documentation Status: Active
	57	source_id	int			For internal use only	Internal	Active
For internal use only Type: Internal Status: Active
	58	temp_sync_status	bit	Default: 0		For internal use only	Internal	Active
For internal use only Default: 0 Type: Internal Status: Active
	59	creation_date	datetime	Default: getdate()		Standard row creation timestamp	Metadata	Active
Standard row creation timestamp Default: getdate() Type: Metadata Status: Active
	60	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
	61	last_modification_date	datetime	Default: getdate()		Standard row last update timestamp	Metadata	Active
Standard row last update timestamp Default: getdate() Type: Metadata Status: Active
	62	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
	63	primary_key (Deprecated)	char(1)	Default: 0		Deprecated	Schema	Deprecated
Deprecated Default: 0 Type: Schema Status: Deprecated
	64	default_def (Deprecated)	nvarchar(4000)			Deprecated	Schema	Deprecated
Deprecated Type: Schema Status: Deprecated
	65	identity_def (Deprecated)	nvarchar(50)			Deprecated	Schema	Deprecated
Deprecated Type: Schema Status: Deprecated
	66	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.columns (Columns)	dbo.schema_updates (Imports/Updates)	dbo.columns.update_id = dbo.schema_updates.update_id	fk_columns_schema_updates
dbo.columns.update_id = dbo.schema_updates.update_id Name: fk_columns_schema_updates
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.columns (Columns)	dbo.columns_changes.column_id = dbo.columns.column_id	fk_columns_changes_columns
dbo.columns_changes.column_id = dbo.columns.column_id Name: fk_columns_changes_columns
dbo.erd_nodes_columns (ERD Nodes Columns)	dbo.columns (Columns)	dbo.erd_nodes_columns.column_id = dbo.columns.column_id	FK_erd_nodes_columns_columns
dbo.erd_nodes_columns.column_id = dbo.columns.column_id Name: FK_erd_nodes_columns_columns
dbo.tables_relations_columns (Tables relations' columns)	dbo.columns (Columns)	dbo.tables_relations_columns.column_fk_id = dbo.columns.column_id	FK_tables_relations_columns_column_fk
dbo.tables_relations_columns.column_fk_id = dbo.columns.column_id Name: FK_tables_relations_columns_column_fk
dbo.tables_relations_columns (Tables relations' columns)	dbo.columns (Columns)	dbo.tables_relations_columns.column_pk_id = dbo.columns.column_id	FK_tables_relations_columns_column_pk
dbo.tables_relations_columns.column_pk_id = dbo.columns.column_id Name: FK_tables_relations_columns_column_pk
dbo.tables_relations_columns_changes	dbo.columns (Columns)	dbo.tables_relations_columns_changes.column_fk_id = dbo.columns.column_id	fk_tables_relations_columns_changes_columns
dbo.tables_relations_columns_changes.column_fk_id = dbo.columns.column_id Name: fk_tables_relations_columns_changes_columns
dbo.tables_relations_columns_changes	dbo.columns (Columns)	dbo.tables_relations_columns_changes.column_pk_id = dbo.columns.column_id	fk_tables_relations_columns_changes_columns
dbo.tables_relations_columns_changes.column_pk_id = dbo.columns.column_id Name: fk_tables_relations_columns_changes_columns
dbo.tables_relations_columns_changes	dbo.columns (Columns)	dbo.tables_relations_columns_changes.before_column_fk_id = dbo.columns.column_id	fk_tables_relations_columns_changes_columns
dbo.tables_relations_columns_changes.before_column_fk_id = dbo.columns.column_id Name: fk_tables_relations_columns_changes_columns
dbo.tables_relations_columns_changes	dbo.columns (Columns)	dbo.tables_relations_columns_changes.before_column_pk_id = dbo.columns.column_id	fk_tables_relations_columns_changes_columns
dbo.tables_relations_columns_changes.before_column_pk_id = dbo.columns.column_id Name: fk_tables_relations_columns_changes_columns
dbo.unique_constraints_columns (Unique keys' columns)	dbo.columns (Columns)	dbo.unique_constraints_columns.column_id = dbo.columns.column_id	FK_unique_constraints_columns_columns
dbo.unique_constraints_columns.column_id = dbo.columns.column_id Name: FK_unique_constraints_columns_columns
dbo.unique_constraints_columns_changes	dbo.columns (Columns)	dbo.unique_constraints_columns_changes.column_id = dbo.columns.column_id	fk_unique_constraints_columns_changes_columns
dbo.unique_constraints_columns_changes.column_id = dbo.columns.column_id Name: fk_unique_constraints_columns_changes_columns
dbo.unique_constraints_columns_changes	dbo.columns (Columns)	dbo.unique_constraints_columns_changes.before_column_id = dbo.columns.column_id	fk_unique_constraints_columns_changes_columns
dbo.unique_constraints_columns_changes.before_column_id = dbo.columns.column_id Name: fk_unique_constraints_columns_changes_columns

Unique keys

	Key name	Columns	Description
	PK_columns	column_id	Table primary key
Table primary key

Triggers

	Key name	When	Description
	trg_columns_change_track_insert	After Insert	Schema change tracking trigger
-- ============================================= -- Description: Insert column's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_columns_change_track_insert] ON [columns] 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 [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [operation], [valid_from], [update_id] ) SELECT [i].[column_id], [i].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [i].[ordinal_position], [i].[name] AS [column_name], [i].[datatype], [i].[data_length], [i].[nullable], [i].[default_value], [i].[is_identity], [i].[is_computed], [i].[computed_formula], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id];
Schema change tracking trigger
-- ============================================= -- Description: Insert column's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_columns_change_track_insert] ON [columns] 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 [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [operation], [valid_from], [update_id] ) SELECT [i].[column_id], [i].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [i].[ordinal_position], [i].[name] AS [column_name], [i].[datatype], [i].[data_length], [i].[nullable], [i].[default_value], [i].[is_identity], [i].[is_computed], [i].[computed_formula], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id];
	trg_columns_change_track_update	After Update	Schema change tracking trigger
-- ============================================= -- Description: Insert column's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_columns_change_track_update] ON [columns] 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].[column_id] = [d].[column_id] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE --isnull(d.ordinal_position,0) <> i.ordinal_position OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length] OR ISNULL([d].[nullable],'') <> [i].[nullable] OR ISNULL([d].[default_value],'') <> [i].[default_value] OR ISNULL([d].[is_identity],'') <> [i].[is_identity] OR ISNULL([d].[is_computed],'') <> [i].[is_computed] OR ISNULL([d].[computed_formula],'') <> [i].[computed_formula] OR [d].[name] <> [i].[name] ) BEGIN UPDATE [columns_changes] SET [valid_to] = GETDATE() FROM [columns_changes] [c] INNER JOIN [inserted] [u] ON [c].[column_id] = [u].[column_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for columns - when column was updated INSERT INTO [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [before_ordinal_position], [before_column_name], [before_datatype], [before_data_length], [before_nullable], [before_default_value], [before_is_identity], [before_is_computed], [before_computed_formula], [operation], [valid_from], [update_id] ) SELECT [i].[column_id], [i].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [i].[ordinal_position], [i].[name] AS [column_name], [i].[datatype], [i].[data_length], [i].[nullable], [i].[default_value], [i].[is_identity], [i].[is_computed], [i].[computed_formula], [d].[ordinal_position], [d].[name] AS [column_name], [d].[datatype], [d].[data_length], [d].[nullable], [d].[default_value], [d].[is_identity], [d].[is_computed], [d].[computed_formula], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[column_id] = [d].[column_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [columns_changes] [c] WHERE [c].[column_id] = [d].[column_id] AND [c].[valid_to] IS NOT NULL ) [cc] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[ordinal_position],0) <> [i].[ordinal_position] OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length] OR ISNULL([d].[nullable],'') <> [i].[nullable] OR ISNULL([d].[default_value],'') <> [i].[default_value] OR ISNULL([d].[is_identity],'') <> [i].[is_identity] OR ISNULL([d].[is_computed],'') <> [i].[is_computed] OR ISNULL([d].[computed_formula],'') <> [i].[computed_formula] OR [d].[name] <> [i].[name]); END; BEGIN -- insert changes (deleted values) for columns - when column was deleted INSERT INTO [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [operation], [valid_to], [update_id] ) SELECT [d].[column_id], [d].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [d].[ordinal_position], [d].[name] AS [column_name], [d].[datatype], [d].[data_length], [d].[nullable], [d].[default_value], [d].[is_identity], [d].[is_computed], [d].[computed_formula], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[column_id] = [d].[column_id] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for columns - when column was restored INSERT INTO [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [operation], [valid_from], [update_id] ) SELECT [i].[column_id], [i].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [i].[ordinal_position], [i].[name] AS [column_name], [i].[datatype], [i].[data_length], [i].[nullable], [i].[default_value], [i].[is_identity], [i].[is_computed], [i].[computed_formula], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[column_id] = [d].[column_id] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
Schema change tracking trigger
-- ============================================= -- Description: Insert column's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_columns_change_track_update] ON [columns] 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].[column_id] = [d].[column_id] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE --isnull(d.ordinal_position,0) <> i.ordinal_position OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length] OR ISNULL([d].[nullable],'') <> [i].[nullable] OR ISNULL([d].[default_value],'') <> [i].[default_value] OR ISNULL([d].[is_identity],'') <> [i].[is_identity] OR ISNULL([d].[is_computed],'') <> [i].[is_computed] OR ISNULL([d].[computed_formula],'') <> [i].[computed_formula] OR [d].[name] <> [i].[name] ) BEGIN UPDATE [columns_changes] SET [valid_to] = GETDATE() FROM [columns_changes] [c] INNER JOIN [inserted] [u] ON [c].[column_id] = [u].[column_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for columns - when column was updated INSERT INTO [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [before_ordinal_position], [before_column_name], [before_datatype], [before_data_length], [before_nullable], [before_default_value], [before_is_identity], [before_is_computed], [before_computed_formula], [operation], [valid_from], [update_id] ) SELECT [i].[column_id], [i].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [i].[ordinal_position], [i].[name] AS [column_name], [i].[datatype], [i].[data_length], [i].[nullable], [i].[default_value], [i].[is_identity], [i].[is_computed], [i].[computed_formula], [d].[ordinal_position], [d].[name] AS [column_name], [d].[datatype], [d].[data_length], [d].[nullable], [d].[default_value], [d].[is_identity], [d].[is_computed], [d].[computed_formula], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[column_id] = [d].[column_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [columns_changes] [c] WHERE [c].[column_id] = [d].[column_id] AND [c].[valid_to] IS NOT NULL ) [cc] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[ordinal_position],0) <> [i].[ordinal_position] OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length] OR ISNULL([d].[nullable],'') <> [i].[nullable] OR ISNULL([d].[default_value],'') <> [i].[default_value] OR ISNULL([d].[is_identity],'') <> [i].[is_identity] OR ISNULL([d].[is_computed],'') <> [i].[is_computed] OR ISNULL([d].[computed_formula],'') <> [i].[computed_formula] OR [d].[name] <> [i].[name]); END; BEGIN -- insert changes (deleted values) for columns - when column was deleted INSERT INTO [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [operation], [valid_to], [update_id] ) SELECT [d].[column_id], [d].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [d].[ordinal_position], [d].[name] AS [column_name], [d].[datatype], [d].[data_length], [d].[nullable], [d].[default_value], [d].[is_identity], [d].[is_computed], [d].[computed_formula], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[column_id] = [d].[column_id] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for columns - when column was restored INSERT INTO [columns_changes] ([column_id], [table_id], [database_id], [table_schema], [table_name], [ordinal_position], [column_name], [datatype], [data_length], [nullable], [default_value], [is_identity], [is_computed], [computed_formula], [operation], [valid_from], [update_id] ) SELECT [i].[column_id], [i].[table_id], [t].[database_id], [t].[schema] AS [table_schema], [t].[name] AS [table_name], [i].[ordinal_position], [i].[name] AS [column_name], [i].[datatype], [i].[data_length], [i].[nullable], [i].[default_value], [i].[is_identity], [i].[is_computed], [i].[computed_formula], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[column_id] = [d].[column_id] LEFT OUTER JOIN [tables] [t] ON [i].[table_id] = [t].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
	trg_columns_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_columns_Modify] ON dbo.columns AFTER INSERT,UPDATE AS BEGIN UPDATE [columns] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE column_id IN (SELECT DISTINCT column_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_columns_Modify] ON dbo.columns AFTER INSERT,UPDATE AS BEGIN UPDATE [columns] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE column_id IN (SELECT DISTINCT column_id FROM Inserted) END

Uses

Name
dbo.columns (Columns)
dbo.schema_updates (Imports/Updates)
dbo.tables (Tables and Views)
dbo.trg_columns_change_track_insert
dbo.columns_changes
dbo.schema_updates (Imports/Updates)
dbo.tables (Tables and Views)
dbo.trg_columns_change_track_update
dbo.columns_changes
dbo.tables (Tables and Views)
dbo.trg_columns_Modify
dbo.columns (Columns)

Used by

Name
dbo.columns (Columns)
dbo.trg_columns_Modify
dbo.trg_tables_relations_cols_change_track_insert
dbo.trg_tables_relations_cols_change_track_update
dbo.trg_unique_constraints_cols_change_track_insert
dbo.trg_unique_constraints_cols_change_track_update
dbo.columns_changes
dbo.erd_nodes_columns (ERD Nodes Columns)
dbo.tables_relations_columns (Tables relations' columns)
dbo.tables_relations_columns (Tables relations' columns)
dbo.tables_relations_columns_changes
dbo.tables_relations_columns_changes
dbo.tables_relations_columns_changes
dbo.tables_relations_columns_changes
dbo.unique_constraints_columns (Unique keys' columns)
dbo.unique_constraints_columns_changes
dbo.unique_constraints_columns_changes

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