JSJ Employee Purge

Overview

This utility script performs a comprehensive database-wide purge of employee records based on workgroup criteria. It identifies employees not in workgroup WG2 = 24, then systematically removes all related data for those employees from every table containing a filekey column. The script provides detailed reporting of deletions performed across all affected tables.

Parameters

The script operates with hardcoded criteria:

• Exclusion Criteria: Employees with WG2 = 24 are preserved
• Target Column: 'filekey' (used to identify employee-related records)
• Scope: All database tables containing a filekey column

Data Components

The script performs several key operations:

1. Employee Identification - Identifies employees to be purged (WG2 NOT IN 24)
2. Table Discovery - Finds all tables with filekey columns
3. Dynamic Deletion - Removes employee records from all relevant tables
4. Results Tracking - Records deletion counts per table
5. Comprehensive Reporting - Displays summary of all deletions

Output Format

The script returns deletion statistics for each affected table:

Only tables with actual deletions (DeletedRows > 0) are displayed in the results.

Technical Implementation

The script uses:

• Dynamic SQL generation with sp_executesql for flexible table operations
• STRING_AGG function to build comma-separated filekey lists
• Cursor-based processing to iterate through all relevant tables
• System catalog views (sys.tables, sys.columns) for table discovery
• Output parameters for row count tracking
• Table variables for results collection

Processing Logic

1. Employee Selection: Build list of filekeys for employees where WG2 ≠ 24
2. Table Discovery: Find all tables containing 'filekey' column
3. Dynamic Deletion: Generate and execute DELETE statements for each table
4. Row Counting: Track number of rows deleted per table
5. Results Compilation: Collect and display deletion statistics

Safety Features

• Workgroup Preservation: Protects employees in WG2 = 24
• Comprehensive Scope: Ensures all related data is removed
• Detailed Reporting: Provides full audit trail of deletions

Notes

• Destructive Operation: Permanently deletes employee data across entire database
• Workgroup Specific: Preserves only employees in WG2 = 24
• Database-Wide Impact: Affects ALL tables containing filekey columns
• Comprehensive Purge: Removes all traces of targeted employees
• Detailed Audit: Provides complete deletion statistics
• Dynamic Processing: Automatically discovers and processes relevant tables
• Referential Integrity: May require careful handling of foreign key constraints
• CRITICAL: Always backup database before running this script
• Test thoroughly on a copy of the database first
• Verify workgroup criteria match your business requirements
• Consider the impact on historical reporting and audit requirements
• Run during maintenance windows to minimize system impact
• Ensure proper authorization before executing this destructive operation
• Review deletion results carefully to verify expected outcomes

T-SQL

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TableName   NVARCHAR(255);
DECLARE @ColumnName  NVARCHAR(255) = 'filekey';
DECLARE @FileKeys    NVARCHAR(MAX);
DECLARE @SQL         NVARCHAR(MAX);
DECLARE @DeletedRows INT;
DECLARE @OutputTable TABLE (
    TableName   NVARCHAR(255),
    DeletedRows INT
);

-- Dynamically build the comma-separated filekey list
SELECT @FileKeys = STRING_AGG(CAST(filekey AS NVARCHAR(MAX)), ',')
FROM employees
WHERE WG2 NOT IN (24);


-- Cursor to go through all tables that have a column named 'filekey'
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 the dynamic SQL using the @FileKeys variable
    SET @SQL = N'
        DELETE FROM ' + QUOTENAME(@TableName) + '
        WHERE ' + QUOTENAME(@ColumnName) + ' IN (' + @FileKeys + ');
        SELECT @DeletedRows = @@ROWCOUNT;
    ';
    EXEC sp_executesql
        @SQL,
        N'@DeletedRows INT OUTPUT',
        @DeletedRows = @DeletedRows OUTPUT;

    IF @DeletedRows > 0
    BEGIN
        INSERT INTO @OutputTable (TableName, DeletedRows)
        VALUES (@TableName, @DeletedRows);
    END;

    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

SELECT * FROM @OutputTable;

Content Inventory

• Doc File: content/docs/utilities/JSJEmpPruge.mdx
• SQL Script: SQL/utilities/JSJEmpPruge.sql