Для этих целей написал SP и вкл. откл. когда нужно.



CREATE PROCEDURE SP_DisEnTrFkCcTab
-- Запрещение, разрешение и показ состояния: триггера(ов),
-- внешнего(их) ключа(ей) и ограничения(й) на значение(я) таблиц(ы);
-- внешнего(их) ключа(ей) на таблицу.
@TypOp char(2)='H*', -- ТИП ДЕЙСТВИЯ:
-- 'D_' - запрещение (disable)
-- 'E_' - разрешение (enable)
-- 'H_'(не 'DE') - показ (select)
-- '_T' - триггер
-- '_F' - внешний ключ
-- '_C' - ограничение на значение
-- '_*' - все 'T'+'F'+'C'
-- '_R' - внешний(е) ключ(и)
-- на таблицу
@NamTb sysname=NULL, -- Имя таблицы; */NULL - для всех
@NamOb sysname=NULL -- об'екта: триггера
-- внешнего ключа
-- ограничения на значение
-- */NULL - все об'екты
AS set nocount on -- Ошибки: 1 - неверный параметр
select @NamTb=ltrim(rtrim(isnull(@NamTb,''))) -- 2 - несуществующий об'ект
if datalength(@NamTb) = 0 -- 3 - нет данных
or @NamTb = '*' select @NamTb=NULL -- 4 - операция не выполнена
select @NamOb=ltrim(rtrim(isnull(@NamOb,'')))
if datalength(@NamOb) = 0 or @NamOb = '*' select @NamOb=NULL
declare @OpTyp char(1),@ObTyp char(1), @TabName sysname,@ObName sysname,
@St int, @Mes varchar(255)
select @OpTyp=isnull(substring(@TypOp,1,1),'H'),
@ObTyp=isnull(substring(@TypOp,2,1),'*')
if @OpTyp not in ('D','E','H') select @OpTyp='H'
if @ObTyp not in ('T','F','C','R','*') select @ObTyp='*'
if @ObTyp in ('T','F','C','*') begin
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@ < ЗАПРЕЩЕНИЕ/РАЗРЕШЕНИЕ/ПОКАЗ ТРИГГЕРА(ОВ),
-- ВНЕШНЕГО(ИХ) КЛЮЧА(ЕЙ) И ОГРАНИЧЕНИЯ(Й)
-- НА ЗНАЧЕНИЕ(Я) ТАБЛИЦ(Ы) > @@@@@@@@@@@@@@
if @OpTyp = 'H' begin -- Показ
select b.name [Table],a.name [Object],'TR' [Type],
isnull(dbo.f_SubObjDesc(b.name,'table',a.name,'trigger'),'') [Description],
case when ObjectProperty(a.id, 'ExecIsTriggerDisabled') = 0 then 'Enabled'
else 'Disabled' end [Status]
from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'TR' and b.xtype = 'U' and b.status >= 0
and b.name = isnull(@NamTb,b.name)
and a.name = isnull(@NamOb,a.name)
and @ObTyp in ('*','T')
union select b.name [Table],a.name [Object],'F' [Type],
isnull(dbo.f_SubObjDesc(b.name,'table',a.name,'constraint'),
dbo.ff_FkDesc(a.name)) [Description],
case when ObjectProperty(a.id,'CnstIsDisabled') = 0 then 'Enabled'
else 'Disabled' end [Status]
from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'F' and b.xtype = 'U' and b.status >= 0
and b.name = isnull(@NamTb,b.name)
and a.name = isnull(@NamOb,a.name)
and @ObTyp in ('*','F')
union select b.name [Table],a.name [Object],'C' [Type],
isnull(dbo.f_SubObjDesc(b.name,'table',a.name,'constraint'),
dbo.ff_CCDesc(a.name)) [Description],
case when ObjectProperty(a.id,'CnstIsDisabled') = 0 then 'Enabled'
else 'Disabled' end [Status]
from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'C' and b.xtype = 'U' and b.status >= 0
and b.name = isnull(@NamTb,b.name)
and a.name = isnull(@NamOb,a.name)
and @ObTyp in ('*','C') order by 1,2,3
return 0 end
-- ............................................................................
if @ObTyp in ('*','T') begin
declare cur_Sel__Tr_DE cursor local for select a.name,b.name,
ObjectProperty(a.id,'ExecIsTriggerDisabled')
from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'TR' and b.xtype = 'U' and b.status >= 0
and b.name = isnull(@NamTb,b.name)
and a.name = isnull(@NamOb,a.name)
if @@ERROR != 0 return 4
open cur_Sel__Tr_DE
if @@ERROR != 0 return 4
fetch next from cur_Sel__Tr_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4
while @@FETCH_STATUS != -1 begin if @@FETCH_STATUS != -2 begin
if @OpTyp = 'D' begin -- Запрещение/Разрешение TR
if @St = 0 exec('alter table '+@TabName+' disable trigger '+ @ObName) end
else if @St = 1 exec('alter table '+@TabName+' enable trigger '+ @ObName) end
if @@ERROR != 0 return 4
-- ........... < ФИКСАЦИЯ В ЖУРНАЛЕ ФАКТА вкл./откл. TR > .................
select @Mes=case when @OpTyp = 'D' and @St = 0 then 'IsDisabled'
when @OpTyp = 'E' and @St = 1 then 'IsEnabled' else NULL end
if @Mes is not NULL begin select @Mes=@ObName+char(134)+@Mes
exec sp_PutJouEvent @ISP=0, @N_ARM=0, @N_EVENT=102002,
@S_EVENT='SP_DisEnTrFkCcTab', @MES=@Mes end
fetch next from cur_Sel__Tr_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4 end
close cur_Sel__Tr_DE deallocate cur_Sel__Tr_DE end
-- ............................................................................
if @ObTyp in ('*','F') begin
declare cur_Sel__Fk_DE cursor local for
select a.name,b.name,ObjectProperty(a.id,'CnstIsDisabled')
from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'F' and b.xtype = 'U' and b.status >= 0
and b.name = isnull(@NamTb,b.name)
and a.name = isnull(@NamOb,a.name)
if @@ERROR != 0 return 4
open cur_Sel__Fk_DE
if @@ERROR != 0 return 4
fetch next from cur_Sel__Fk_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4
while @@FETCH_STATUS != -1 begin if @@FETCH_STATUS != -2 begin
if @OpTyp = 'D' begin -- Запрещение/Разрешение FK
if @St = 0 exec('alter table '+@TabName+' nocheck constraint '+@ObName) end
else if @St = 1 exec('alter table '+@TabName+' check constraint '+@ObName) end
if @@ERROR != 0 return 4
-- ........... < ФИКСАЦИЯ В ЖУРНАЛЕ ФАКТА вкл./откл. FK > .................
select @Mes=case when @OpTyp = 'D' and @St = 0 then 'IsNoCheck'
when @OpTyp = 'E' and @St = 1 then 'IsCheck' else NULL end
if @Mes is not NULL begin select @Mes=@ObName+char(134)+@Mes
exec sp_PutJouEvent @ISP=0, @N_ARM=0, @N_EVENT=102003,
@S_EVENT='SP_DisEnTrFkCcTab', @MES=@Mes end
fetch next from cur_Sel__Fk_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4 end
close cur_Sel__Fk_DE deallocate cur_Sel__Fk_DE end
-- ............................................................................
if @ObTyp in ('*','C') begin
declare cur_Sel__Cc_DE cursor local for select a.name,b.name,
ObjectProperty(a.id,'CnstIsDisabled')
from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'C' and b.xtype = 'U' and b.status >= 0
and b.name = isnull(@NamTb,b.name)
and a.name = isnull(@NamOb,a.name)
if @@ERROR != 0 return 4
open cur_Sel__Cc_DE
if @@ERROR != 0 return 4
fetch next from cur_Sel__Cc_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4
while @@FETCH_STATUS != -1 begin if @@FETCH_STATUS != -2 begin
if @OpTyp = 'D' begin -- Запрещение/Разрешение CC
if @St = 0 exec('alter table '+@TabName+' nocheck constraint '+@ObName) end
else if @St = 1 exec('alter table '+@TabName+' check constraint '+@ObName) end
if @@ERROR != 0 return 4
-- ........... < ФИКСАЦИЯ В ЖУРНАЛЕ ФАКТА вкл./откл. CC > .................
select @Mes=case when @OpTyp = 'D' and @St = 0 then 'IsNoCheck'
when @OpTyp = 'E' and @St = 1 then 'IsCheck' else NULL end
if @Mes is not NULL begin select @Mes=@ObName+char(134)+@Mes
exec sp_PutJouEvent @ISP=0, @N_ARM=0, @N_EVENT=102004,
@S_EVENT='SP_DisEnTrFkCcTab', @MES=@Mes end
fetch next from cur_Sel__Cc_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4 end
close cur_Sel__Cc_DE deallocate cur_Sel__Cc_DE end
return 0 end
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@ < ЗАПРЕЩЕНИЕ/РАЗРЕШЕНИЕ/ПОКАЗ ВНЕШНЕГО(ИХ)
-- КЛЮЧА(ЕЙ) НА ТАБЛИЦУ > @@@@@@@@@@@@@@@@@
-- @NamOb - определяет FK (ссылку на @NamTb) коннкретной таблицы
if @NamTb is not NULL begin
if @OpTyp = 'H' begin -- Показ
select Object_Name(fkeyid) [Table],Object_Name(constid) [Object],'F' [Type],
isnull(dbo.f_SubObjDesc(Object_Name(fkeyid),'table',
Object_Name(constid),'constraint'),
dbo.ff_FkDesc(Object_Name(constid))) [Description],
case when ObjectProperty(constid,'CnstIsDisabled') = 0 then 'Enabled'
else 'Disabled' end [Status]
from sysreferences a, sysobjects b where rkeyid = Object_Id(@NamTb)
and b.id = constid and xtype = 'F'
and fkeyid = isnull(Object_Id(@NamOb),fkeyid)
order by 2
return 0 end
-- ............................................................................
declare cur_Sel__FkRefTb_DE cursor local for select Object_Name(constid),
Object_Name(fkeyid),ObjectProperty(constid,'CnstIsDisabled')
from sysreferences a, sysobjects b where rkeyid = Object_Id(@NamTb)
and b.id = constid and xtype = 'F'
and fkeyid = isnull(Object_Id(@NamOb),fkeyid)
if @@ERROR != 0 return 4
open cur_Sel__FkRefTb_DE
if @@ERROR != 0 return 4
fetch cur_Sel__FkRefTb_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4
while @@FETCH_STATUS != -1 begin if @@FETCH_STATUS != -2 begin
if @OpTyp = 'D' begin -- Запрещение/Разрешение FK
if @St = 0 exec('alter table '+@TabName+' nocheck constraint '+@ObName) end
else if @St = 1 exec('alter table '+@TabName+' check constraint '+@ObName) end
if @@ERROR != 0 return 4
-- ........... < ФИКСАЦИЯ В ЖУРНАЛЕ ФАКТА вкл./откл. FK > .................
select @Mes=case when @OpTyp = 'D' and @St = 0 then 'IsNoCheck'
when @OpTyp = 'E' and @St = 1 then 'IsCheck' else NULL end
if @Mes is not NULL begin select @Mes=@ObName+char(134)+@Mes
exec sp_PutJouEvent @ISP=0, @N_ARM=0, @N_EVENT=102003,
@S_EVENT='SP_DisEnTrFkCcTab', @MES=@Mes end
fetch cur_Sel__FkRefTb_DE into @ObName,@TabName,@St
if @@ERROR != 0 return 4 end
close cur_Sel__FkRefTb_DE deallocate cur_Sel__FkRefTb_DE end
-- ============================================================================
return 0
-- #### (c) "PolySystem" 08.02.02 ##########################################



GO