Single Table Purge

Script: scripts/utilities/SingleTablePurge.sql

Overview

This utility script removes orphaned records from a single specified table by deleting rows where the filekey does not exist in the EMPLOYEES table. It's designed for targeted cleanup of specific tables that may contain outdated employee references after employee deletions or data migrations.

Parameters

• @TableName - The name of the table to purge (default: 'ARCTRANSACTIONS')
• @ColumnName - The column name containing employee references (default: 'filekey')

Data Components

The script performs:

1. Orphaned Record Identification - Finds records with invalid employee references
2. Targeted Deletion - Removes only the orphaned records
3. Change Tracking - Records the number of rows deleted
4. Results Reporting - Displays summary of cleanup performed

Output Format

Only displays results if records were actually deleted.

Technical Implementation

The script uses:

• Dynamic SQL with sp_executesql for flexible table operations
• NOT IN subquery to identify orphaned records
• Row count tracking with @@ROWCOUNT
• Conditional output to show results only when changes occur
• Table variables for result storage and formatting

Notes

• Single Table Focus: Designed for targeted cleanup of one table at a time
• Configurable: Easy to modify for different tables and column names
• Safe Operation: Only removes records with invalid employee references
• Data Integrity: Helps maintain referential integrity
• Selective Cleanup: More controlled than bulk purge operations
• Performance: Faster than multi-table operations for single table cleanup
• Verification: Shows exact count of records removed
• Reversible: Only if you have backed up the data beforehand
• Always backup the target table before running
• Verify the table name and column name before execution
• Consider running the SELECT version first to preview what will be deleted
• Use during maintenance windows for production environments

T-SQL

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TableName   NVARCHAR(255) = N'ARCTRANSACTIONS'; -- <-- Set your table name here
DECLARE @ColumnName  NVARCHAR(255) = 'filekey';
DECLARE @SQL         NVARCHAR(MAX);
DECLARE @DeletedRows INT;
DECLARE @OutputTable TABLE (
    TableName   NVARCHAR(255),
    DeletedRows INT
);

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

SELECT * FROM @OutputTable;

Content Inventory

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