IF OBJECT_ID('dev.uspModifyDictionaryReferenceId') IS NOT NULL BEGIN
  DROP PROCEDURE dev.uspModifyDictionaryReferenceId
END;
GO
CREATE PROCEDURE dev.uspModifyDictionaryReferenceId
   @newId INT
    ,@name nvarchar(200)  --dictionary név
  ,@type nvarchar(100)  --dictionary típusa
  ,@suffix nvarchar(200)='_002'

AS
BEGIN
DECLARE
   @ParentTableName nvarchar(100)
  ,@itemIds nvarchar(4000)
  ,@ReferencingTableName nvarchar(400)
  ,@ReferencingColumnName nvarchar(400)
  ,@ReferencingObjectID nvarchar(400)
  ,@sql nvarchar(max)

SELECT @itemIds = ISNULL(@itemIds + ',', '') + cast(ID AS varchar(10)) 
FROM T_DICTIONARYITEMBASE td 
    WHERE td.C_NAME=@name+@suffix
    AND td.C_TYPE=@type
    AND td.TOROLT='F'

SET @ParentTableName='T_'+@type
DECLARE ReferencedTables CURSOR FOR 
  SELECT 
    OBJECT_NAME(pc.[object_id]) AS referencing_object_name
    ,pc.[name] AS referencing_column_name
  FROM sys.foreign_key_columns f 
    INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
    INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
  WHERE pc.[name] NOT IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID', 'ID')
    AND rc.[name] NOT IN (N'C_ALTANEVID', N'C_TANEVID')
    AND OBJECT_NAME(rc.[object_id]) = @ParentTableName

OPEN ReferencedTables
FETCH NEXT FROM ReferencedTables INTO @ReferencingTableName,@ReferencingColumnName
WHILE @@FETCH_STATUS = 0
BEGIN

  SET @sql= N'
    UPDATE '+ @ReferencingTableName + N' 
    SET '+@ReferencingColumnName + N' = @newId 
    WHERE '+@ReferencingColumnName + N' IN (' + @itemIds + N')'
  
  PRINT @sql
  EXEC sp_executesql @sql, N'@newId int', @newId

  FETCH NEXT FROM ReferencedTables INTO @ReferencingTableName,@ReferencingColumnName
END

CLOSE ReferencedTables
DEALLOCATE ReferencedTables
END