CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Robert
Robert

Reputation: 1726

Find duplicate values over multiple databases

I'm trying to find a way to find duplicate email addresses in a table over multiple DB's. I have 30 DB's, all with a "Users" table with identical fields (each DB is a different customer of ours). I need to go through each DB, get the email address from this table, then search the current, and all other DB's for this email address. If it exists, write out the record, if not, move on to the next user record and repeat. Here's what I've got so far, but I'm stuck. I think I have to create a cursor inside another cursor but don't know if it can be done dynamically:

DECLARE @DBName varchar(20)
DECLARE @sSQL      varchar(2000)

DECLARE LoopCursor CURSOR FOR
    SELECT DBName
    FROM Configuration

    OPEN LoopCursor

    FETCH NEXT FROM LoopCursor
    INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sSQL = '
            Select EmailAddress
            FROM ' + @DBName + '.dbo.Users'

        --Not to sure what to do from here??

        EXEC(@sSQL)

        FETCH NEXT FROM LoopCursor
        INTO @DBName
    END

    CLOSE LoopCursor
DEALLOCATE LoopCursor

SELECT 'DONE'

We are using SQL Server 2008 R2 btw.

Upvotes: 3

Views: 2746

Answers (1)

Laurence
Laurence

Reputation: 10976

Not tested, but something like this should dump them all into whatever table you put in db.schema.DuplicateEmailAddresses

Declare @DBName varchar(20)
Declare @sql nvarchar(max)
Declare @join nvarchar(max) = ''

Declare LoopCursor Cursor For
    Select DBName
    From Configuration

Set @sql = N'Select EmailAddress Into db.schema.DuplicateEmailAddresses From ('

Open LoopCursor

Fetch Next From LoopCursor Into @DBName

While  @@FETCH_STATUS = 0
Begin
    Set @sql = @sql + @join + N'Select EmailAddress From ' + QuoteName(@DBName) + N'.dbo.Users'
    Set @join = N' Union All '
    Fetch Next From LoopCursor Into @DBName
End

Close LoopCursor
Deallocate LoopCursor

Set @sql = @sql + N') as tmp Group By EmailAddress Having Count(*) > 1'

exec sp_executesql @sql, N''

Upvotes: 2

Related Questions