JSJ Employee Purge - File Key Not On Employee Table

Overview

This utility script performs orphaned record cleanup by removing all records from tables that contain filekey values not present in the EMPLOYEES table. It systematically scans every table with a filekey column (except the EMPLOYEES table itself) and deletes records that reference non-existent employees. This script is essential for maintaining referential integrity and cleaning up orphaned data after employee deletions.

Parameters

The script operates with hardcoded criteria:

• Target Column: 'filekey' (used to identify employee-related records)
• Reference Table: EMPLOYEES table (source of valid filekeys)
• Scope: All database tables containing a filekey column (except EMPLOYEES)
• Cleanup Rule: Remove records where filekey NOT IN (SELECT filekey FROM employees)

Data Components

The script performs several key operations:

1. Table Discovery - Finds all tables with filekey columns (excluding EMPLOYEES)
2. Orphan Detection - Identifies records with filekeys not in EMPLOYEES table
3. Dynamic Deletion - Removes orphaned 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
• NOT IN subquery to identify orphaned records
• Cursor-based processing to iterate through all relevant tables
• System catalog views (sys.tables, sys.columns) for table discovery
• Table exclusion logic to skip the EMPLOYEES table
• Output parameters for row count tracking

Processing Logic

1. Table Discovery: Find all tables with filekey columns except EMPLOYEES
2. Orphan Identification: Use NOT IN clause to find invalid filekeys
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

• Reference Integrity: Only removes records with invalid filekey references
• EMPLOYEES Protection: Excludes EMPLOYEES table from processing
• Comprehensive Scope: Ensures all orphaned data is removed
• Detailed Reporting: Provides full audit trail of deletions

Notes

• Data Integrity Tool: Removes orphaned records to maintain referential integrity
• Comprehensive Cleanup: Processes all tables with filekey columns
• EMPLOYEES Protection: Never modifies the EMPLOYEES table itself
• Orphan Detection: Uses NOT IN logic to identify invalid references
• Database-Wide Impact: Affects ALL tables containing filekey columns
• Detailed Audit: Provides complete deletion statistics
• Dynamic Processing: Automatically discovers and processes relevant tables
• Safe Operation: Only removes records with invalid references
• RECOMMENDED: Always backup database before running this script
• Run this script after employee deletions to clean up orphaned data
• Verify the results to ensure expected orphaned records were removed
• Consider the impact on historical reporting if orphaned data is needed
• Run during maintenance windows to minimize system impact
• Use this script regularly as part of database maintenance procedures
• Monitor results to identify tables with frequent orphaned data
• Consider adding foreign key constraints to prevent future orphaned records

T-SQL

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

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

-- 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
      AND t.name <> 'employees'; -- Don't process the employees table itself

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build the dynamic SQL to delete rows where filekey is not in employees
    SET @SQL = N'
        DELETE FROM ' + QUOTENAME(@TableName) + '
        WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (SELECT filekey FROM employees);
        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/JSJEmpPrugeFileKeyNotOnEmpTable.mdx
• SQL Script: SQL/utilities/JSJEmpPrugeFileKeyNotOnEmpTable.sql