Single Table Purge with Row Limit

Overview

This utility script performs controlled orphaned record cleanup on a single specified table by removing records with filekey values not present in the EMPLOYEES table. It uses batch processing with a configurable row limit to safely delete large numbers of orphaned records without overwhelming the database. This script is ideal for cleaning up specific tables with known orphaned data issues.

Parameters

The script requires manual configuration of key parameters:

• @TableName - Target table name (default: 'YourTableNameHere' - MUST BE CHANGED)
• @ColumnName - Column to check for orphaned values (default: 'filekey')
• @BatchSize - Maximum number of rows to delete in single operation (default: 100,000)
• Cleanup Rule: Remove records where filekey NOT IN (SELECT filekey FROM employees)

Data Components

The script performs several key operations:

1. Parameter Configuration - Sets target table, column, and batch size
2. Orphan Detection - Identifies records with filekeys not in EMPLOYEES table
3. Batch Deletion - Removes orphaned records up to the specified limit
4. Results Tracking - Records deletion count for the processed table
5. Results Reporting - Displays summary of deletions performed

Output Format

The script returns deletion statistics for the processed table:

Only displays results if deletions were performed (DeletedRows > 0).

Technical Implementation

The script uses:

• Dynamic SQL generation with sp_executesql for flexible table operations
• TOP clause for batch size control
• NOT IN subquery to identify orphaned records
• Configurable parameters for table name and batch size
• Output parameters for row count tracking
• Table variables for results storage

Processing Logic

1. Parameter Setup: Configure table name, column name, and batch size
2. Dynamic SQL Generation: Build DELETE statement with TOP clause
3. Orphan Identification: Use NOT IN clause to find invalid filekeys
4. Batch Deletion: Execute DELETE with row limit
5. Results Collection: Track and display deletion statistics

Safety Features

• Batch Processing: Limits number of rows deleted in single operation
• Reference Integrity: Only removes records with invalid filekey references
• Single Table Focus: Targets only the specified table
• Configurable Limits: Allows control over deletion batch size
• Results Reporting: Provides audit trail of deletions

Notes

• Configuration Required: Must set @TableName before execution
• Batch Processing: Processes limited number of rows per execution
• Single Table Operation: Only processes one table at a time
• Orphan Detection: Uses NOT IN logic to identify invalid references
• Repeatable: Can be run multiple times to process large datasets
• Controlled Impact: Batch size limits database load
• Safe Operation: Only removes records with invalid references
• Performance Friendly: Avoids overwhelming database with large deletions
• CRITICAL: Set @TableName to actual table name before execution
• Test on a small batch size first to verify expected behavior
• Run multiple times if more orphaned records exist than batch size
• Monitor database performance during execution
• Consider running during maintenance windows for large tables
• Verify the results to ensure expected orphaned records were removed
• Adjust batch size based on table size and system performance
• Use this script when you need controlled cleanup of specific tables

T-SQL

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

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

SET @SQL = N'
    DELETE TOP (' + CAST(@BatchSize AS NVARCHAR(20)) + ') FROM ' + QUOTENAME(@TableName) + '
    WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (SELECT filekey FROM employees);
    SELECT @DeletedRows = @@ROWCOUNT;
';

SET @DeletedRows = 0;
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/SingleTablePurgeWRowLimit.mdx
• SQL Script: SQL/utilities/SingleTablePurgeWRowLimit.sql