create procedure sys.sp_refreshsqlmodule_internal
@name nvarchar(776),
@namespace nvarchar(20), -- OBJECT, SERVER_DDL_TRIGGER, DATABASE_DDL_TRIGGER
@viewonly bit
as
-- DECLARE VARIABLES
declare @objid int
declare @type char(2)
declare @ns int
-- ============================================================
-- DEVNOTE:
-- x_eonc_TrgOnServer = 20, // Namespace for Triggers on the Server
-- x_eonc_TrgOnDatabase = 21, // Namespace for Triggers on Databases
-- x_eonc_Standard = 0 // Standard Namespace
-- ============================================================
set @ns = case lower(@namespace collate Latin1_General_CI_AS)
when N'object' then 0
when N'server_ddl_trigger' then 20
when N'database_ddl_trigger' then 21
else -1
end
-- DDL Trigger must be a one part name
if(@ns = 20 or @ns = 21)
begin
if(parsename(@name, 2) IS NOT NULL or parsename(@name, 3) IS NOT NULL or parsename(@name, 4) IS NOT NULL)
begin
raiserror(15165,-1, -1, @name)
return @@error
end
end
-- RESOLVE GIVEN OBJECT NAME
if(@ns = 0) -- Object
begin
select @objid = m.object_id, @type = o.type from sys.sql_modules m
join sys.objects o on o.object_id = m.object_id
where o.object_id = object_id(@name, 'local')
and o.type in ('P', 'FN', 'IF', 'TF', 'V', 'TR')
and m.is_schema_bound = 0
and (@viewonly = 0 or o.type = 'V')
end
else if(@ns = 21 and @viewonly = 0) -- Database DDL trigger
begin
select @objid = t.object_id, @type = t.type from sys.triggers t where t.name = parsename(@name, 1) -- handle quoted identifier
end
else if(@ns = 20 and @viewonly = 0) -- Server DDL trigger
begin
select @objid = t.object_id, @type = t.type from sys.server_triggers t where t.name = parsename(@name, 1) -- handle quoted identifier
end
if @objid is null
begin
raiserror(15165,-1,-1,@name)
return @@error
end
BEGIN TRANSACTION
-- LOCK OBJECT & CHECK PERMISSION
if(@type = 'TR')
EXEC %%TriggerEx(MultiName = @name).LockMatchID(ID = @objid, Exclusive = 1, NameSpaceClass = @ns)
else
EXEC %%Object(MultiName = @name).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
if @@error <> 0
begin
COMMIT TRANSACTION
raiserror(15165,-1,-1,@name)
return @@error
end
-- REFRESH MODULE DEFINITION
EXEC %%Module(ID = @objid).Refresh(NameSpaceClass = @ns)
if @@error <> 0
begin
COMMIT TRANSACTION
return @@error
end
COMMIT TRANSACTION
-- RETURN SUCCESS
RETURN 0 -- sp_refreshsqlmodule_internal