spMFRemoveAdditionalProperties

Signature

EXEC dbo.spMFRemoveAdditionalProperties
    @MFTableName = N'MFCustomer',
    @Columns = NULL,
    @ProcessBatch_ID = NULL OUTPUT,
    @Debug = 0;

Returns

INT: 1 on success; -1 on error.

Parameters

@MFTableName

  • Type: NVARCHAR(128)

  • Required: Yes

  • Description: Valid class table name (e.g., ‘MFCustomer’).

@Columns

  • Type: NVARCHAR(MAX)

  • Required: No (default = NULL)

  • Description: When NULL, remove all empty additional property columns not on the metadata card; otherwise, comma-separated list of specific columns to remove.

@ProcessBatch_ID

  • Type: INT

  • Required: No (OUTPUT)

  • Description: ProcessBatch logging ID.

@Debug

  • Type: INT

  • Required: No (default = 0)

  • Description: 1 = Standard debug.

Purpose

M-Files allows for properties to be added to the metadata card as additional properties. Sometimes these properties becomes redundant. The Connector will automatically create columns for properties when they are used. Over time this may add many columns on the class table that is no longer used or relevant. This procedure will allow for identifying the empty columns, validated that they are no longer included in the metadata card specification and remove them.

Additional Info

By default the column will only be removed if all data has been removed. Property columns where property MFID < 1000 is ignored.

do a normal update from SQL to MF by setting the data in unwanted columns to null, and set the process_id = 1 to remove the data in unwanted columns.

When @Column is null then all additional property columns with null data will be removed.

To remove columns where the property MFID < 1000 the column must be specified e.g. @Columns = ‘Is_Template’

Examples

deleting additional columns where all data is null

DECLARE @ProcessBatch_ID1 INT;

EXEC dbo.spMFRemoveAdditionalProperties @MFTableName = 'MFOtherDocument',
@ProcessBatch_ID = @ProcessBatch_ID1 OUTPUT,
@Debug = 1

Deleting specified columns

DECLARE @ProcessBatch_ID1 INT;

EXEC dbo.spMFRemoveAdditionalProperties @MFTableName = 'MFOtherDocument',
@columns = 'Is_Template',
@ProcessBatch_ID = @ProcessBatch_ID1 OUTPUT,
@Debug = 1

Changelog

Date

Author

Description

2020-12-19

LC

Create procedure