贴个存储过程啦！

数据库内部实现外键表属性的局部更改

 

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER   proc Entity_ItemUpdateForPropertyAndUtilityName
(
 @Entity_ID int,
 @Entity_Name nvarchar(200)
)
AS

--获取原始的EntityName----------------------------------
declare @OldEntity_Name nvarchar (200)
select @OldEntity_Name = Entity_Name from Entity
where Entity_ID=@Entity_ID
-------------------------------------------------------

--实体类属性列表-----------------------------------
create table #PropertyTemp
(
 Property_ID int
)

insert into #PropertyTemp
select Property_ID from Property where Property_EntityID =  @Entity_ID

--select * from #PropertyTemp
---------------------------------------------------

--实体类存储过程列表----------------------------------
create table #UtiltityTemp
(
 Utility_ID int
)
insert into #UtiltityTemp
select Utility_ID from Utility where Utility_EntityID = @Entity_ID
-----------------------------------------------------

--------------更新所有实体类属性的名字前缀------------------
declare @TmpProperty_ID int
declare cursorPropertyID cursor
for select * from #PropertyTemp
 open cursorPropertyID
  fetch cursorPropertyID into @TmpProperty_ID
  while @@Fetch_Status = 0
  begin
   update Property
    set Property_PropertyName = replace ( Property_PropertyName , @OldEntity_Name,@Entity_Name)
   where Property_ID = @TmpProperty_ID
   
  -- select @OldEntity_Name,@Entity_Name
   fetch cursorPropertyID into @TmpProperty_ID
   
  end
  
 close cursorPropertyID
deallocate cursorPropertyID
--------------------------------------------------------

-----------------更新所有实体类存储过程的名字前缀-------------
declare @TmpUtility_ID int
declare cursorUtilityID cursor
for select * from #UtiltityTemp
 open cursorUtilityID
  fetch cursorUtilityID into @TmpUtility_ID
  while @@Fetch_Status = 0
  begin
   --select @TmpUtility_ID
   update Utility
    set Utility_UtilityName= replace ( Utility_UtilityName,@OldEntity_Name,@Entity_Name)
   where Utility_ID = @TmpUtility_ID
   fetch cursorUtilityID into @TmpUtility_ID
  end
 close cursorUtilityID
deallocate cursorUtilityID
------------------------------------------------------------

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO