Quick startup for multiple class tables

This guide shows how to initialize a list of classes, create class tables, and update them in a single run. Logging and statistics are included for each step.

Class List Setup

Edit the list of classes to add class tables to your project, then run the script.

DECLARE @ClassList NVARCHAR(4000) = 'Customer, Contact Person, Document'

Metadata Synchronization

Synchronize the metadata structure before creating tables.

EXEC dbo.spMFDropAndUpdateMetadata
    @IsResetAll = 0,
    @ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
    @Debug = 0,
    @WithClassTableReset = 0;

Class Table Creation

Create a table for each class in your list.

-- Table variable to store class and table names
DECLARE @ClassTable AS TABLE (
    id INT IDENTITY,
    ClassName NVARCHAR(100),
    TableName NVARCHAR(100)
);

INSERT INTO @ClassTable (ClassName, TableName)
SELECT mc.Name, mc.TableName
FROM dbo.fnMFParseDelimitedString(@ClassList, ',') AS fmpds
INNER JOIN dbo.MFClass AS mc ON fmpds.ListItem = mc.Name;

SELECT * FROM @ClassTable;

-- Loop to create tables
DECLARE @Rownr INT = 1;
DECLARE @MFClassName NVARCHAR(100);
DECLARE @Query NVARCHAR(MAX);

WHILE @Rownr IS NOT NULL
BEGIN
    SELECT @MFClassName = ct.ClassName FROM @ClassTable AS ct WHERE id = @Rownr;
    SET @Query = N'EXEC dbo.spMFCreateTable @ClassName = ' + QUOTENAME(@MFClassName) + ', @Debug = 0;';
    EXEC (@Query);
    SELECT @Rownr = (SELECT MIN(id) FROM @ClassTable AS ct WHERE id > @Rownr);
END

Class Table Update

Update each class table and show results, stats, and errors if any.

DECLARE @MFTableName NVARCHAR(100);
DECLARE @ProcessBatch_ID INT;
DECLARE @Update_IDOut INT;
DECLARE @Return_Value SMALLINT;
SET @Rownr = 1;

WHILE @Rownr IS NOT NULL
BEGIN
    SELECT @MFTableName = ct.TableName FROM @ClassTable AS ct WHERE id = @Rownr;
    EXEC @Return_Value = dbo.spMFUpdateTable
        @MFTableName = @MFTableName,
        @UpdateMethod = 1,
        @Update_IDOut = @Update_IDOut OUTPUT,
        @ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
        @Debug = 0;

    SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID;
    SELECT @Return_Value AS Return_Value;

    EXEC dbo.spMFClassTableStats
        @ClassTableName = @MFTableName,
        @Flag = 0,
        @WithReset = 0,
        @IncludeOutput = 0,
        @Debug = 0;

    IF @Return_Value <> 1
    BEGIN
        SELECT TOP 1 ml.ErrorMessage, ml.CreateDate, *
        FROM dbo.MFLog AS ml
        ORDER BY ml.LogID;
    END

    EXEC (N'SELECT * FROM ' + @MFTableName);
    SELECT @Rownr = (SELECT MIN(id) FROM @ClassTable AS ct WHERE id > @Rownr);
END

Show All Classes

SELECT * FROM dbo.MFClass;

Drop All Class Tables (Optional)

-- To drop all class tables, run:
EXEC dbo.spMFDropAllClassTables @IncludeInApp = 1, @Debug = 0;