Tabellen mit doppelten RecIDs ermitteln

use ax2009_standard_sp1

set nocount on

declare @tableName char(100)
declare db_cursor CURSOR FOR 
 select name from dbo.sysobjects
 where xtype = 'U'
 and name <> 'ROWSTAT'
 and name <> 'ROWSTATUS'
 and name <> 'SQLSYSTEMVARIABLES'
 and name <> 'SYSTEMSEQUENCES' -- SYSTEMSEQUENCES enthält immer doppelte RecIDs
 order by name asc

-- Temporäre Tabelle aufbauen
IF OBJECT_ID(N'tempdb..#tmp_duplrecid', N'U') IS NOT NULL
 begin
 drop table #tmp_duplrecid
 end

create table #tmp_duplrecid (tablename char(100), recordcounter bigint)   

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tableName  

WHILE @@FETCH_STATUS = 0  
 BEGIN  
  -- Tabellen mit doppelten RecIds ermitteln
  exec('insert into #tmp_duplrecid select ''' + @tableName + ''', COUNT(*)' +
    ' from ' + @tableName +
    ' group by RECID ' +
    ' having COUNT(*) > 1')
       
  FETCH NEXT FROM db_cursor INTO @tableName  
 END  

CLOSE db_cursor  
DEALLOCATE db_cursor

set nocount off

-- Tabellen mit doppelten RecIDs
select 'Tabelle enthält doppelte RecIDs: ' + tablename
from #tmp_duplrecid