Orphaned Rows Detection

Script: scripts/utilities/OrphanedRows.sql

Overview

This utility script identifies orphaned records across all database tables that contain a 'filekey' column. It finds records where the filekey value does not exist in the EMPLOYEES table, indicating potential data integrity issues or records that should be cleaned up after employee deletions.

Parameters

• @ColumnName - The column name to check for orphaned references (default: 'filekey')
• The script automatically excludes the EMPLOYEES table from the analysis

Data Components

The script performs several operations:

1. Table Discovery - Identifies all tables containing the specified column
2. Orphaned Record Detection - Counts records with invalid foreign key references
3. Results Compilation - Aggregates findings into a summary report

Output Format

Results are ordered by OrphanedRows in descending order (tables with most orphans first).

Technical Implementation

The script uses:

• Dynamic SQL with sp_executesql for flexible table querying
• Cursor-based processing to iterate through all relevant tables
• System catalog views (sys.tables, sys.columns) for table discovery
• Table variables for temporary storage and output formatting
• NOT IN subqueries to identify orphaned references

Notes

• Data Integrity Check: Identifies referential integrity violations
• Automatic Discovery: Finds all tables with filekey columns automatically
• Performance Consideration: May take time on large databases with many tables
• Cleanup Planning: Results help prioritize data cleanup efforts
• Employee Table Exclusion: Automatically excludes the master EMPLOYEES table
• Zero Results: Tables with no orphaned records are not displayed
• Maintenance Tool: Useful for regular database health checks
• Consider running during off-peak hours for large databases
• Results can guide data cleanup and referential integrity improvements
• May indicate need for proper foreign key constraints if not already implemented

T-SQL

SET NOCOUNT ON;

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

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'; -- Exclude the employees table

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
        SELECT @RowCount = COUNT(*)
        FROM ' + QUOTENAME(@TableName) + '
        WHERE ' + QUOTENAME(@ColumnName) + ' NOT IN (SELECT filekey FROM employees);
    ';
    SET @RowCount = 0;
    EXEC sp_executesql
        @SQL,
        N'@RowCount INT OUTPUT',
        @RowCount = @RowCount OUTPUT;

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

    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

SELECT * FROM @OutputTable
ORDER BY OrphanedRows DESC;

Content Inventory

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