sql server 修改被引用的方法

有一个被很多地方引用导致没办法修改：Cannot ALTER 'dbo.F_GetLocalDate' because it is being referenced by object 'DF_mms_member_AddDate'.

网上没找到合适方法，发挥自己动手丰衣足食的传统
首先理一下思路：

1，先把引用到方法的地方修改掉

2，修改方法

3，引用到方法的地方修改回来

嗯，说干就干

-------------------------------------------华丽的分割线--------------------------------------------------

-- 欲查詢之schema, function

declare @pSchemaName varchar(30) = '',
@pFunctionName varchar(30) = ''

select 
    LiteralCheckOrDefault,
    s.Name as SchemaName,
    t.Name as TableName,
    cn.Name as ConstraintName,
    cl.Name as ColumnName,
    Definition
into #cnTemp
from (
select 'check' as LiteralCheckOrDefault, 
Name, Definition, parent_object_id, parent_column_id 
from sys.check_constraints
union all
select 'default' as LiteralCheckOrDefault, 
Name, Definition, parent_object_id, parent_column_id 
from sys.default_constraints
) cn
inner join sys.columns cl
on cn.parent_object_id = cl.object_id AND
    cn.parent_column_id = cl.column_id
inner join sys.tables t
on t.object_id = cl.object_id
inner join sys.schemas S
on t.schema_id = s.schema_id
where definition like '%'+@pSchemaName+'%'+@pFunctionName+'%'
order by SchemaName, TableName, ColumnName, ConstraintName

-- 建立刪除語法
select 'alter table [' + SchemaName + '].[' + TableName + '] ' +
'drop constraint [' + ConstraintName + '];' as Drop_Command
,SchemaName
,TableName
,ColumnName
,ConstraintName
,Definition
from #cnTemp
order by SchemaName ,TableName ,ColumnName

-- 執行function修改
-- 將約束條件建回
select 'alter table [' + SchemaName + '].[' + TableName + '] ' + 
'add constraint [' + ConstraintName + '] ' + LiteralCheckOrDefault + ' ' + 
Definition + 
iif(LiteralCheckOrDefault = 'default', ' for [' + ColumnName+ ']', '') + ';' as ADD_Command  
from #cnTemp
order by SchemaName ,TableName ,ColumnName

drop table #cnTemp

------------------------------------F_GetLocalDate------------------------------------------------------

ALTER FUNCTION [dbo].[F_GetLocalDate]

(
    @TimezoneDiffInHour TINYINT = 8
)

RETURNS DATETIME

AS

BEGIN
    declare @result datetime
    set @result=''
select @result=DATEADD(Hh, @TimezoneDiffInHour , GETUTCDATE())
RETURN @result
END

欢迎转载，请注明来自白菜卖故事>>本文地址：http://www.bcmgs.com/786

0 还没人赞过...