dbo.parameters (Parameters) - Dataedo repository 7

Dataedo repository 7

- Dataedo repository 7
  - Modules
  - Tables

Documentation	Dataedo repository 7
Schema	dbo
Name	parameters
Title	Parameters
Module	Schema and Metadata
Type	Schema
Status	Active

Function and procedure parameters.

Columns

		Name	Data type	Attributes	References	Description	Type	Status
	1	parameter_id	int	Identity		Row id	Metadata	Active
Row id Identity Type: Metadata Status: Active
	2	procedure_id	int		dbo.procedures (Procedures and Functions)	Parameter's procedure	Metadata	Active
Parameter's procedure References: dbo.procedures (Procedures and Functions) Type: Metadata Status: Active
	3	name	nvarchar(250)			Parameter name, "Returns" for functions output	Schema	Active
Parameter name, "Returns" for functions output Type: Schema Status: Active
	4	ordinal_position	int			Position of parameter in parameters list	Schema	Active
Position of parameter in parameters list Type: Schema Status: Active
	5	parameter_mode	nvarchar(10)			IN, OUT, INOUT	Schema	Active
IN, OUT, INOUT Type: Schema 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 parameter was deleted from DBMS, 'A' otherwise	Metadata	Active
'D' if parameter was deleted from DBMS, 'A' otherwise Default: 'A' Type: Metadata Status: Active
	8	description	nvarchar(MAX)			Parameter description defined with Dataedo	Documentation	Active
Parameter description defined with Dataedo Type: Documentation Status: Active
	9	datatype	nvarchar(250)			Column data type	Schema	Active
Column data type Type: Schema Status: Active
	10	data_length	nvarchar(20)			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
	11	field1	nvarchar(MAX)			Custom field 1	Documentation	Active
Custom field 1 Type: Documentation Status: Active
	12	field2	nvarchar(MAX)			Custom field 2	Documentation	Active
Custom field 2 Type: Documentation Status: Active
	13	field3	nvarchar(MAX)			Custom field 3	Documentation	Active
Custom field 3 Type: Documentation Status: Active
	14	field4	nvarchar(MAX)			Custom field 4	Documentation	Active
Custom field 4 Type: Documentation Status: Active
	15	field5	nvarchar(MAX)			Custom field 5	Documentation	Active
Custom field 5 Type: Documentation Status: Active
	16	field6	nvarchar(MAX)			Custom field 6	Documentation	Active
Custom field 6 Type: Documentation Status: Active
	17	field7	nvarchar(MAX)			Custom field 7	Documentation	Active
Custom field 7 Type: Documentation Status: Active
	18	field8	nvarchar(MAX)			Custom field 8	Documentation	Active
Custom field 8 Type: Documentation Status: Active
	19	field9	nvarchar(MAX)			Custom field 9	Documentation	Active
Custom field 9 Type: Documentation Status: Active
	20	field10	nvarchar(MAX)			Custom field 10	Documentation	Active
Custom field 10 Type: Documentation Status: Active
	21	field11	nvarchar(MAX)			Custom field 11	Documentation	Active
Custom field 11 Type: Documentation Status: Active
	22	field12	nvarchar(MAX)			Custom field 12	Documentation	Active
Custom field 12 Type: Documentation Status: Active
	23	field13	nvarchar(MAX)			Custom field 13	Documentation	Active
Custom field 13 Type: Documentation Status: Active
	24	field14	nvarchar(MAX)			Custom field 14	Documentation	Active
Custom field 14 Type: Documentation Status: Active
	25	field15	nvarchar(MAX)			Custom field 15	Documentation	Active
Custom field 15 Type: Documentation Status: Active
	26	field16	nvarchar(MAX)			Custom field 16	Documentation	Active
Custom field 16 Type: Documentation Status: Active
	27	field17	nvarchar(MAX)			Custom field 17	Documentation	Active
Custom field 17 Type: Documentation Status: Active
	28	field18	nvarchar(MAX)			Custom field 18	Documentation	Active
Custom field 18 Type: Documentation Status: Active
	29	field19	nvarchar(MAX)			Custom field 19	Documentation	Active
Custom field 19 Type: Documentation Status: Active
	30	field20	nvarchar(MAX)			Custom field 20	Documentation	Active
Custom field 20 Type: Documentation Status: Active
	31	field21	nvarchar(MAX)			Custom field 21	Documentation	Active
Custom field 21 Type: Documentation Status: Active
	32	field22	nvarchar(MAX)			Custom field 22	Documentation	Active
Custom field 22 Type: Documentation Status: Active
	33	field23	nvarchar(MAX)			Custom field 23	Documentation	Active
Custom field 23 Type: Documentation Status: Active
	34	field24	nvarchar(MAX)			Custom field 24	Documentation	Active
Custom field 24 Type: Documentation Status: Active
	35	field25	nvarchar(MAX)			Custom field 25	Documentation	Active
Custom field 25 Type: Documentation Status: Active
	36	field26	nvarchar(MAX)			Custom field 26	Documentation	Active
Custom field 26 Type: Documentation Status: Active
	37	field27	nvarchar(MAX)			Custom field 27	Documentation	Active
Custom field 27 Type: Documentation Status: Active
	38	field28	nvarchar(MAX)			Custom field 28	Documentation	Active
Custom field 28 Type: Documentation Status: Active
	39	field29	nvarchar(MAX)			Custom field 29	Documentation	Active
Custom field 29 Type: Documentation Status: Active
	40	field30	nvarchar(MAX)			Custom field 30	Documentation	Active
Custom field 30 Type: Documentation Status: Active
	41	field31	nvarchar(MAX)			Custom field 31	Documentation	Active
Custom field 31 Type: Documentation Status: Active
	42	field32	nvarchar(MAX)			Custom field 32	Documentation	Active
Custom field 32 Type: Documentation Status: Active
	43	field33	nvarchar(MAX)			Custom field 33	Documentation	Active
Custom field 33 Type: Documentation Status: Active
	44	field34	nvarchar(MAX)			Custom field 34	Documentation	Active
Custom field 34 Type: Documentation Status: Active
	45	field35	nvarchar(MAX)			Custom field 35	Documentation	Active
Custom field 35 Type: Documentation Status: Active
	46	field36	nvarchar(MAX)			Custom field 36	Documentation	Active
Custom field 36 Type: Documentation Status: Active
	47	field37	nvarchar(MAX)			Custom field 37	Documentation	Active
Custom field 37 Type: Documentation Status: Active
	48	field38	nvarchar(MAX)			Custom field 38	Documentation	Active
Custom field 38 Type: Documentation Status: Active
	49	field39	nvarchar(MAX)			Custom field 39	Documentation	Active
Custom field 39 Type: Documentation Status: Active
	50	field40	nvarchar(MAX)			Custom field 40	Documentation	Active
Custom field 40 Type: Documentation Status: Active
	51	creation_date	datetime	Default: getdate()		Standard row creation timestamp	Metadata	Active
Standard row creation timestamp Default: getdate() Type: Metadata Status: Active
	52	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
	53	last_modification_date	datetime	Default: getdate()		Standard row last update timestamp	Metadata	Active
Standard row last update timestamp Default: getdate() Type: Metadata Status: Active
	54	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
	55	source_id	int			For internal use only	Internal	Active
For internal use only Type: Internal Status: Active
	56	temp_sync_status	bit	Default: 0		For internal use only	Internal	Active
For internal use only Default: 0 Type: Internal Status: Active
	57	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.parameters (Parameters)	dbo.procedures (Procedures and Functions)	dbo.parameters.procedure_id = dbo.procedures.procedure_id	FK_parameters_procedures
dbo.parameters.procedure_id = dbo.procedures.procedure_id Name: FK_parameters_procedures
dbo.parameters (Parameters)	dbo.schema_updates (Imports/Updates)	dbo.parameters.update_id = dbo.schema_updates.update_id	fk_parameters_schema_updates
dbo.parameters.update_id = dbo.schema_updates.update_id Name: fk_parameters_schema_updates
dbo.parameters_changes	dbo.parameters (Parameters)	dbo.parameters_changes.parameter_id = dbo.parameters.parameter_id	fk_parameters_changes_parameters
dbo.parameters_changes.parameter_id = dbo.parameters.parameter_id Name: fk_parameters_changes_parameters

Unique keys

	Key name	Columns	Description
	PK_parameters	parameter_id	Table primary key
Table primary key

Triggers

	Key name	When	Description
	trg_parameters_change_track_insert	After Insert	Schema change tracking trigger
-- ============================================= -- Description: Insert parameter's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_parameters_change_track_insert] ON [parameters] FOR INSERT AS INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [operation], [valid_from], [update_id] ) SELECT [p].[database_id], [i].[parameter_id], [i].[procedure_id], [i].[ordinal_position], [i].[parameter_mode], [i].[name], [i].[datatype], [i].[data_length], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] JOIN [procedures] [p] ON [i].[procedure_id] = [p].[procedure_id] WHERE NOT EXISTS ( SELECT 1 FROM [schema_updates] [u] WHERE [u].[update_id] = [i].[update_id] AND [u].[type] = 'IMPORT' );
Schema change tracking trigger
-- ============================================= -- Description: Insert parameter's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_parameters_change_track_insert] ON [parameters] FOR INSERT AS INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [operation], [valid_from], [update_id] ) SELECT [p].[database_id], [i].[parameter_id], [i].[procedure_id], [i].[ordinal_position], [i].[parameter_mode], [i].[name], [i].[datatype], [i].[data_length], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] JOIN [procedures] [p] ON [i].[procedure_id] = [p].[procedure_id] WHERE NOT EXISTS ( SELECT 1 FROM [schema_updates] [u] WHERE [u].[update_id] = [i].[update_id] AND [u].[type] = 'IMPORT' );
	trg_parameters_change_track_update	After Update	Schema change tracking trigger
-- ============================================= -- Description: Insert parameter's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_parameters_change_track_update] ON [parameters] 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].[parameter_id] = [d].[parameter_id] WHERE --isnull(d.[name],'') <> i.[name] or ISNULL([d].[ordinal_position],'') <> [i].[ordinal_position] OR ISNULL([d].[parameter_mode],'') <> [i].[parameter_mode] OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length] ) BEGIN UPDATE [parameters_changes] SET [valid_to] = GETDATE() FROM [parameters_changes] [c] INNER JOIN [inserted] [u] ON [c].[procedure_id] = [u].[procedure_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for parameter - when parameter was updated INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [before_ordinal_position], [before_parameter_mode], [before_datatype], [before_data_length], [operation], [valid_from], [update_id] ) SELECT [p].[database_id], [i].[parameter_id], [i].[procedure_id], [i].[ordinal_position], [i].[parameter_mode], [i].[name], [i].[datatype], [i].[data_length], [d].[ordinal_position], [d].[parameter_mode], [d].[datatype], [d].[data_length], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[parameter_id] = [d].[parameter_id] JOIN [procedures] [p] ON [d].[procedure_id] = [p].[procedure_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [procedures_changes] [c] WHERE [c].[procedure_id] = [d].[procedure_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[ordinal_position],'') <> [i].[ordinal_position] OR ISNULL([d].[parameter_mode],'') <> [i].[parameter_mode] OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length]); END; BEGIN -- insert changes (deleted values) for parameter - when parameter was deleted INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [operation], [valid_to], [update_id] ) SELECT [p].[database_id], [d].[parameter_id], [d].[procedure_id], [d].[ordinal_position], [d].[parameter_mode], [d].[name], [d].[datatype], [d].[data_length], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[parameter_id] = [d].[parameter_id] JOIN [procedures] [p] ON [d].[procedure_id] = [p].[procedure_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for parameter - when parameter was restored INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [operation], [valid_from], [update_id] ) SELECT [p].[database_id], [i].[parameter_id], [i].[procedure_id], [i].[ordinal_position], [i].[parameter_mode], [i].[name], [i].[datatype], [i].[data_length], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[parameter_id] = [d].[parameter_id] JOIN [procedures] [p] ON [d].[procedure_id] = [p].[procedure_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
Schema change tracking trigger
-- ============================================= -- Description: Insert parameter's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_parameters_change_track_update] ON [parameters] 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].[parameter_id] = [d].[parameter_id] WHERE --isnull(d.[name],'') <> i.[name] or ISNULL([d].[ordinal_position],'') <> [i].[ordinal_position] OR ISNULL([d].[parameter_mode],'') <> [i].[parameter_mode] OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length] ) BEGIN UPDATE [parameters_changes] SET [valid_to] = GETDATE() FROM [parameters_changes] [c] INNER JOIN [inserted] [u] ON [c].[procedure_id] = [u].[procedure_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for parameter - when parameter was updated INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [before_ordinal_position], [before_parameter_mode], [before_datatype], [before_data_length], [operation], [valid_from], [update_id] ) SELECT [p].[database_id], [i].[parameter_id], [i].[procedure_id], [i].[ordinal_position], [i].[parameter_mode], [i].[name], [i].[datatype], [i].[data_length], [d].[ordinal_position], [d].[parameter_mode], [d].[datatype], [d].[data_length], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[parameter_id] = [d].[parameter_id] JOIN [procedures] [p] ON [d].[procedure_id] = [p].[procedure_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [procedures_changes] [c] WHERE [c].[procedure_id] = [d].[procedure_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[ordinal_position],'') <> [i].[ordinal_position] OR ISNULL([d].[parameter_mode],'') <> [i].[parameter_mode] OR ISNULL([d].[datatype],'') <> [i].[datatype] OR ISNULL([d].[data_length],'') <> [i].[data_length]); END; BEGIN -- insert changes (deleted values) for parameter - when parameter was deleted INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [operation], [valid_to], [update_id] ) SELECT [p].[database_id], [d].[parameter_id], [d].[procedure_id], [d].[ordinal_position], [d].[parameter_mode], [d].[name], [d].[datatype], [d].[data_length], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[parameter_id] = [d].[parameter_id] JOIN [procedures] [p] ON [d].[procedure_id] = [p].[procedure_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for parameter - when parameter was restored INSERT INTO [parameters_changes] ([database_id], [parameter_id], [procedure_id], [ordinal_position], [parameter_mode], [name], [datatype], [data_length], [operation], [valid_from], [update_id] ) SELECT [p].[database_id], [i].[parameter_id], [i].[procedure_id], [i].[ordinal_position], [i].[parameter_mode], [i].[name], [i].[datatype], [i].[data_length], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[parameter_id] = [d].[parameter_id] JOIN [procedures] [p] ON [d].[procedure_id] = [p].[procedure_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
	trg_parameters_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_parameters_Modify] ON [parameters] AFTER INSERT, UPDATE AS BEGIN UPDATE [parameters] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE parameter_id IN (SELECT DISTINCT parameter_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_parameters_Modify] ON [parameters] AFTER INSERT, UPDATE AS BEGIN UPDATE [parameters] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE parameter_id IN (SELECT DISTINCT parameter_id FROM Inserted) END

Uses

Name
dbo.parameters (Parameters)
dbo.procedures (Procedures and Functions)
dbo.schema_updates (Imports/Updates)
dbo.trg_parameters_change_track_insert
dbo.parameters_changes
dbo.procedures (Procedures and Functions)
dbo.schema_updates (Imports/Updates)
dbo.trg_parameters_change_track_update
dbo.parameters_changes
dbo.procedures (Procedures and Functions)
dbo.procedures_changes
dbo.trg_parameters_Modify
dbo.parameters (Parameters)

Used by

Name
dbo.parameters (Parameters)
dbo.trg_parameters_Modify
dbo.parameters_changes

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