JSJ Employee Purge - Top 20

Overview

This utility script performs a limited database-wide purge of employee records, targeting only the first 20 employees that meet the workgroup criteria (WG2 NOT IN 24). It systematically removes all related data for these specific employees from every table containing a filekey column. This script is a safer, limited version of the full employee purge utility, designed for testing or gradual cleanup operations.

Parameters

The script operates with hardcoded criteria:

• Exclusion Criteria: Employees with WG2 = 24 are preserved
• Limit: Only processes the first 20 qualifying employees
• 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 Selection - Identifies top 20 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:

• Common Table Expression (CTE) with TOP 20 for limited selection
• 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

Processing Logic

1. Limited Selection: Use CTE with TOP 20 to select first 20 qualifying employees
2. Filekey List Building: Create comma-separated list of selected filekeys
3. Table Discovery: Find all tables containing 'filekey' column
4. Dynamic Deletion: Generate and execute DELETE statements for each table
5. Row Counting: Track number of rows deleted per table
6. Results Compilation: Collect and display deletion statistics

Safety Features

• Limited Scope: Only processes 20 employees at a time
• Workgroup Preservation: Protects employees in WG2 = 24
• Comprehensive Scope: Ensures all related data is removed for selected employees
• Detailed Reporting: Provides full audit trail of deletions

Notes

• Limited Destructive Operation: Permanently deletes data for 20 employees only
• Workgroup Specific: Preserves only employees in WG2 = 24
• Database-Wide Impact: Affects ALL tables containing filekey columns
• Controlled Purge: Removes all traces of selected 20 employees
• Detailed Audit: Provides complete deletion statistics
• Dynamic Processing: Automatically discovers and processes relevant tables
• Testing Friendly: Safer alternative to full employee purge for testing
• Gradual Cleanup: Can be run multiple times for incremental cleanup
• 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
• Use this script for testing before running the full employee purge
• Monitor results to ensure expected employees are being processed
• Consider running multiple times if more than 20 employees need purging

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
);

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

-- 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_Top20.mdx
• SQL Script: SQL/utilities/JSJEmpPruge_Top20.sql