Workgroup Reassignment Scripts

Scripts:

scripts/utilities/wg1reassign.sql
scripts/utilities/wg2reassign.sql
scripts/utilities/wg3reassign.sql
scripts/utilities/wg4reassign.sql
scripts/utilities/wg5reassign.sql

Overview

These utility scripts perform bulk workgroup reassignments across all database tables containing workgroup columns (WG1, WG2, WG3, WG4, WG5). Each script standardizes workgroup values by updating non-standard workgroup codes to a default value, ensuring data consistency across the entire database.

Parameters

Each script operates on:

Target Column: WG1, WG2, WG3, WG4, or WG5 respectively
Excluded Values: Specific workgroup codes that should remain unchanged
Default Value: Standard workgroup code to assign (typically 32)

Specific Configurations

WG1: Updates all values except 22, 23, 24 to 32
WG2: Updates all values except 22, 23, 24 to 32
WG3: Updates all values except 22, 23, 24 to 32
WG4: Updates all values except 22, 23, 24 to 32
WG5: Updates all values except 22, 23, 24 to 32

Data Components

Each script performs:

Table Discovery - Identifies all tables containing the target workgroup column
Bulk Updates - Updates non-standard workgroup values to the default
Change Tracking - Records the number of rows updated per table
Results Reporting - Displays summary of changes made

Output Format

Column	Data Type	Description
TableName	nvarchar(255)	Name of table where updates occurred
UpdatedRows	int	Number of rows updated in that table

Only tables with actual updates are displayed in the results.

Technical Implementation

The scripts use:

Dynamic SQL with sp_executesql for flexible table updates
Cursor-based processing to iterate through all relevant tables
System catalog views (sys.tables, sys.columns) for table discovery
Conditional updates to preserve specific workgroup values
Row count tracking with @@ROWCOUNT

Notes

Database-Wide Impact: These scripts affect ALL tables containing workgroup columns
Preservation Logic: Maintains specific workgroup codes (22, 23, 24) unchanged
Standardization: Ensures consistent workgroup coding across the database
Batch Processing: Handles multiple tables automatically
Change Tracking: Provides detailed reporting of modifications
Data Integrity: Maintains referential relationships while standardizing codes
Performance: May take time on large databases with many tables
Backup Recommended: Always backup data before running these scripts
Run during maintenance windows to minimize impact on operations
Verify workgroup assignments after completion
Consider testing on a copy of the database first

T-SQL

wg1reassign.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TableName   NVARCHAR(255);
DECLARE @ColumnName  NVARCHAR(255) = 'WG1';
DECLARE @SQL         NVARCHAR(MAX);
DECLARE @UpdatedRows INT;

-- Track output
DECLARE @OutputTable TABLE (
    TableName   NVARCHAR(255),
    UpdatedRows INT
);

-- Cursor to loop through all tables containing column 'WG1'
DECLARE table_cursor CURSOR FOR
    SELECT t.name AS TableName
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = @ColumnName;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL for update
    SET @SQL = N'
        UPDATE ' + QUOTENAME(@TableName) + '
        SET ' + QUOTENAME(@ColumnName) + ' = 32
        WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (22,23,24);
        SELECT @UpdatedRows = @@ROWCOUNT;
    ';

    -- Execute dynamic SQL
    EXEC sp_executesql
        @SQL,
        N'@UpdatedRows INT OUTPUT',
        @UpdatedRows = @UpdatedRows OUTPUT;

    -- Log if any rows were updated
    IF @UpdatedRows > 0
    BEGIN
        INSERT INTO @OutputTable (TableName, UpdatedRows)
        VALUES (@TableName, @UpdatedRows);
    END;

    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

-- Output result
SELECT * FROM @OutputTable;

wg2reassign.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TableName   NVARCHAR(255);
DECLARE @ColumnName  NVARCHAR(255) = 'WG2';
DECLARE @SQL         NVARCHAR(MAX);
DECLARE @UpdatedRows INT;

-- Track output
DECLARE @OutputTable TABLE (
    TableName   NVARCHAR(255),
    UpdatedRows INT
);

-- Cursor to loop through all tables containing column 'WG1'
DECLARE table_cursor CURSOR FOR
    SELECT t.name AS TableName
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = @ColumnName;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL for update
    SET @SQL = N'
        UPDATE ' + QUOTENAME(@TableName) + '
        SET ' + QUOTENAME(@ColumnName) + ' = 24
        WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (24);
        SELECT @UpdatedRows = @@ROWCOUNT;
    ';

    -- Execute dynamic SQL
    EXEC sp_executesql
        @SQL,
        N'@UpdatedRows INT OUTPUT',
        @UpdatedRows = @UpdatedRows OUTPUT;

    -- Log if any rows were updated
    IF @UpdatedRows > 0
    BEGIN
        INSERT INTO @OutputTable (TableName, UpdatedRows)
        VALUES (@TableName, @UpdatedRows);
    END;

    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

-- Output result
SELECT * FROM @OutputTable;

wg3reassign.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TableName   NVARCHAR(255);
DECLARE @ColumnName  NVARCHAR(255) = 'WG3';
DECLARE @SQL         NVARCHAR(MAX);
DECLARE @UpdatedRows INT;

-- Track output
DECLARE @OutputTable TABLE (
    TableName   NVARCHAR(255),
    UpdatedRows INT
);

-- Cursor to loop through all tables containing column 'WG3'
DECLARE table_cursor CURSOR FOR
    SELECT t.name AS TableName
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = @ColumnName;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL for update
    SET @SQL = N'
        UPDATE ' + QUOTENAME(@TableName) + '
        SET ' + QUOTENAME(@ColumnName) + ' = 230
        WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (65,70,71,84,142,143,153,161,165,168,170,172,189,193,194,201,221,230);
        SELECT @UpdatedRows = @@ROWCOUNT;
    ';

    -- Execute dynamic SQL
    EXEC sp_executesql
        @SQL,
        N'@UpdatedRows INT OUTPUT',
        @UpdatedRows = @UpdatedRows OUTPUT;

    -- Log if any rows were updated
    IF @UpdatedRows > 0
    BEGIN
        INSERT INTO @OutputTable (TableName, UpdatedRows)
        VALUES (@TableName, @UpdatedRows);
    END;

    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

-- Output result
SELECT * FROM @OutputTable;

wg4reassign.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TableName   NVARCHAR(255);
DECLARE @ColumnName  NVARCHAR(255) = 'WG4';
DECLARE @SQL         NVARCHAR(MAX);
DECLARE @UpdatedRows INT;

-- Track output
DECLARE @OutputTable TABLE (
    TableName   NVARCHAR(255),
    UpdatedRows INT
);

-- Cursor to loop through all tables containing column 'WG4'
DECLARE table_cursor CURSOR FOR
    SELECT t.name AS TableName
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = @ColumnName;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL for update
    SET @SQL = N'
        UPDATE ' + QUOTENAME(@TableName) + '
        SET ' + QUOTENAME(@ColumnName) + ' = 753
        WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (510,507,500,498,721,503,509,535,533,534,531,532,530,514,502,508,505,499,496,506,722,528,501,497,495,504,753);
        SELECT @UpdatedRows = @@ROWCOUNT;
    ';

    -- Execute dynamic SQL
    EXEC sp_executesql
        @SQL,
        N'@UpdatedRows INT OUTPUT',
        @UpdatedRows = @UpdatedRows OUTPUT;

    -- Log if any rows were updated
    IF @UpdatedRows > 0
    BEGIN
        INSERT INTO @OutputTable (TableName, UpdatedRows)
        VALUES (@TableName, @UpdatedRows);
    END;

    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

-- Output result
SELECT * FROM @OutputTable;

wg5reassign.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TableName   NVARCHAR(255);
DECLARE @ColumnName  NVARCHAR(255) = 'WG5';
DECLARE @SQL         NVARCHAR(MAX);
DECLARE @UpdatedRows INT;

-- Track output
DECLARE @OutputTable TABLE (
    TableName   NVARCHAR(255),
    UpdatedRows INT
);

-- Cursor to loop through all tables containing column 'WG5'
DECLARE table_cursor CURSOR FOR
    SELECT t.name AS TableName
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = @ColumnName;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL for update
    SET @SQL = N'
        UPDATE ' + QUOTENAME(@TableName) + '
        SET ' + QUOTENAME(@ColumnName) + ' = 2201
        WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (2201, 1031, 1028, 1029, 2042);
        SELECT @UpdatedRows = @@ROWCOUNT;
    ';

    -- Execute dynamic SQL
    EXEC sp_executesql
        @SQL,
        N'@UpdatedRows INT OUTPUT',
        @UpdatedRows = @UpdatedRows OUTPUT;

    -- Log if any rows were updated
    IF @UpdatedRows > 0
    BEGIN
        INSERT INTO @OutputTable (TableName, UpdatedRows)
        VALUES (@TableName, @UpdatedRows);
    END;

    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

-- Output result
SELECT * FROM @OutputTable;

Content Inventory

Doc File: content/docs/utilities/workgroup_reassignment_scripts.mdx
SQL Script: SQL/utilities/workgroup_code_correction_report.sql

Workgroup Reassignment Scripts

On this page