How to Find Duplicate Records in All the Tables in SQL Server Database ?

Find Duplicate Records in All the Tables in SQL Server

To find duplicate records in all tables of a SQL Server database, you can use a combination of GROUP BY and HAVING clauses along with dynamic SQL to iterate through all tables and columns. Here's an example script:

DECLARE @TableName NVARCHAR(255)
DECLARE @ColumnName NVARCHAR(255)
DECLARE @DuplicateCount INT

DECLARE tables_cursor CURSOR FOR
SELECT table_name = t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE = 'BASE TABLE'

OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE columns_cursor CURSOR FOR
    SELECT column_name = c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = @TableName

    OPEN columns_cursor
    FETCH NEXT FROM columns_cursor INTO @ColumnName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Dynamic SQL to find duplicate records for each column in the current table
        DECLARE @DynamicSQL NVARCHAR(MAX)
        SET @DynamicSQL = 
            'SELECT ' + QUOTENAME(@ColumnName) + ', COUNT(*) AS DuplicateCount ' +
            'FROM ' + QUOTENAME(@TableName) + ' ' +
            'GROUP BY ' + QUOTENAME(@ColumnName) + ' ' +
            'HAVING COUNT(*) > 1'

        -- Execute the dynamic SQL
        EXEC sp_executesql @DynamicSQL

        FETCH NEXT FROM columns_cursor INTO @ColumnName
    END

    CLOSE columns_cursor
    DEALLOCATE columns_cursor

    FETCH NEXT FROM tables_cursor INTO @TableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor