Мы, чтобы формировать файлы с новыми текстами проц., функц. и триггеров, сделали приложение, вызывающее
процедуру:

CREATE PROCEDURE SP_GenScr
-- Создание SQL скрипта создания/удаления об'екта.
@typ varchar(2)=NULL, -- Тип об'екта:
-- 'T' - таблицы (tables)
-- 'V' - представления (views)
-- 'P' - процедуры (procedures)
-- 'F' - функции (functions)
-- 'TR' - триггера (triggers)
-- 'PK' - первичные ключи (primary key)
-- 'IX' - индексы (index)
-- 'FK' - внешние ключи (foreign key)
-- 'CC' - ограничния на значения
-- (check constraint)
-- 'DF' - значения по умолчанию (default)
@frm varchar(4)='CD', -- Тип скрипта:
-- 'CD' - удаление и создание (drop & create)
-- 'C' - создание (create)
-- 'D' - удаление (drop)
-- '-N' - нумеровать строки - вставка в начало
-- фрагмента '--nnnnnnnnnn.# '
-- '-X' - xml формат
@obj sysname=NULL -- Имя об'екта
-- Ошибки: 1 - неверный параметр
-- ****** 2 - несуществующий об'ект
-- 3 - скрипт не создан
AS set nocount on -- 4 - ошибка SQL сервера
declare @ID int, @r int, @p int, @OB sysname, @TTT sysname, @TXT varchar(4000),
@COLID int, @S varchar(1000), @Y_DR tinyint,@Y_CR tinyint,
@j int, @IDN int, @x char(1),@IDF int, @Y_NL tinyint
declare @ResTab table (i int NOT NULL identity,
t varchar(300) collate database_default NULL)
-- 000000000000000000000000000000000000000000000000000000000000000000000000000
if upper(isnull(replace(@typ,' ',''),'*')) not in ('T','V','P','F','TR','PK',
'IX','FK','CC','DF') begin
insert into @ResTab values ('-- Параметры процедуры:')
insert into @ResTab values ('-- --------------------')
insert into @ResTab values ('@typ -- Тип объекта:')
insert into @ResTab values (' -- ''T'' - таблицы (tables)')
insert into @ResTab values (' -- ''V'' - представления (views)')
insert into @ResTab values (' -- ''P'' - процедуры (procedures)')
insert into @ResTab values (' -- ''F'' - функции (functions)')
insert into @ResTab values (' -- ''TR'' - триггера (triggers)')
insert into @ResTab values (' -- ''PK'' - первичные ключи (primary key)')
insert into @ResTab values (' -- ''IX'' - индексы (index)')
insert into @ResTab values (' -- ''FK'' - внешние ключи (foreign key)')
insert into @ResTab values (' -- ''CC'' - ограничния на значения')
insert into @ResTab values (' -- (check constraint)')
insert into @ResTab values
(' -- ''DF'' - значения по умолчанию (default)')
insert into @ResTab values ('@frm varchar(2)=''CD'', -- Формат скрипта:')
insert into @ResTab values (' -- ''C'' - создание (create)')
insert into @ResTab values (' -- ''D'' - удаление (drop)')
insert into @ResTab values
(' -- ''CD'' - удаление и создание (drop & create)')
insert into @ResTab values ('@obj sysname=NULL -- Имя об''екта')
select t from @ResTab order by i return 1 end
-- ...........................................................................
select @Y_CR=case when charindex('C', upper(isnull(@frm,'CD'))) = 0 then 0
else 1 end
select @Y_DR=case when charindex('D', upper(isnull(@frm,'CD'))) = 0 then 0
else 1 end
select @Y_NL=case when charindex('-N',upper(isnull(@frm,'CD'))) != 0 then 1
when charindex('-X',upper(isnull(@frm,'CD'))) != 0 then 2
else 0 end
if @Y_CR = 0 begin -- __DROP_DROP_DROP_DROP_DROP_DROP_DROP_DROP_DROP___>
-- Удаление об'ектов
select @S='-- Drop Objects. (c) "PolySystem" Kyiv '+
convert(varchar(10),getdate(),104)+' '+convert(varchar(5),getdate(),8)
insert into @ResTab values (@S)
insert into @ResTab values ('-- ==================================='+
space(15)+'================')
insert into @ResTab values ('go') insert into @ResTab values ('')
-- TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT
if upper(@typ) = 'T' begin declare cur_DrpTablDB cursor local for
select name from sysobjects where xtype = 'U' and status >= 0
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = isnull(@obj,name) order by name
open cur_DrpTablDB fetch next from cur_DrpTablDB into @OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin
-- REFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREF
declare cur_DrpDepTablDB cursor local for select a.constid,b.parent_obj
from sysreferences a,sysobjects b where a.rkeyid = object_id(@OB)
and a.constid = b.id and b.xtype = 'F'
open cur_DrpDepTablDB fetch next from cur_DrpDepTablDB into @r,@p
while @@FETCH_STATUS >= 0 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+object_name(@r)+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsForeignKey'') = 1)')
insert into @ResTab values ('alter table '+object_name(@p)+
' drop constraint '+object_name(@r))
insert into @ResTab values ('go')
fetch next from cur_DrpDepTablDB into @r,@p end
close cur_DrpDepTablDB deallocate cur_DrpDepTablDB
-- REFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREF
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsUserTable'') = 1)'+
' drop table '+@OB)
insert into @ResTab values ('go') end
fetch next from cur_DrpTablDB into @OB end
close cur_DrpTablDB deallocate cur_DrpTablDB end
-- VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
if upper(@typ) = 'V' begin declare cur_DrpViewDB cursor local for
select name from sysobjects where xtype = 'V' and status >= 0
and OBJECTPROPERTY(id, N'IsView') = 1
and name = isnull(@obj,name) order by name
open cur_DrpViewDB fetch next from cur_DrpViewDB into @OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsView'') = 1)'+
' drop view '+@OB)
insert into @ResTab values ('go') end
fetch next from cur_DrpViewDB into @OB end
close cur_DrpViewDB deallocate cur_DrpViewDB end

-- PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP
if upper(@typ) = 'P' begin declare cur_DrpProcDB cursor local for
select name from sysobjects where xtype ='P' and status >= 0
and OBJECTPROPERTY(id, N'IsProcedure') = 1
and name = isnull(@obj,name) order by name
open cur_DrpProcDB fetch next from cur_DrpProcDB into @OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsProcedure'') = 1)'+
' drop procedure '+@OB)
insert into @ResTab values ('go') end
fetch next from cur_DrpProcDB into @OB end
close cur_DrpProcDB deallocate cur_DrpProcDB end
-- FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
if upper(@typ) = 'F' begin declare cur_DrpFuncDB cursor local for
select name from sysobjects where status >= 0
and (xtype ='FN' or xtype ='IF' or xtype ='TF')
and (OBJECTPROPERTY(id, N'IsScalarFunction') = 1
or OBJECTPROPERTY(id, N'IsInlineFunction') = 1
or OBJECTPROPERTY(id, N'IsTableFunction') = 1)
and name = isnull(@obj,name) order by name
open cur_DrpFuncDB fetch next from cur_DrpFuncDB into @OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values
('(OBJECTPROPERTY(id, N''IsScalarFunction'') = 1 or'+
' OBJECTPROPERTY(id, N''IsInlineFunction'') = 1 or')
insert into @ResTab values
(' OBJECTPROPERTY(id, N''IsTableFunction'') = 1))'+
' drop function '+@OB)
insert into @ResTab values ('go') end
fetch next from cur_DrpFuncDB into @OB end
close cur_DrpFuncDB deallocate cur_DrpFuncDB end
-- TRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRT
if upper(@typ) = 'TR' begin declare cur_DrpTrigDB cursor local for
select a.name from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'TR' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(a.id, N'IsTrigger') = 1
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_DrpTrigDB fetch next from cur_DrpTrigDB into @OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsTrigger'') = 1)'+
' drop trigger '+@OB)
insert into @ResTab values ('go') end
fetch next from cur_DrpTrigDB into @OB end
close cur_DrpTrigDB deallocate cur_DrpTrigDB end
-- PKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKP
if upper(@typ) = 'PK' begin declare cur_DrpPkeDB cursor local for
select name,object_name(id) from sysindexes where indid > 0 and indid < 255
and (status & 64) = 0 and (status & 2048) != 0
and exists(select '*' from sysobjects
where id = sysindexes.id
and xtype = 'U' and status > 0
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
and name = isnull(@obj,name) order by name
open cur_DrpPkeDB
fetch next from cur_DrpPkeDB into @OB,@TTT
while @@FETCH_STATUS >= 0 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsPrimaryKey'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go')
fetch next from cur_DrpPkeDB into @OB,@TTT end
close cur_DrpPkeDB deallocate cur_DrpPkeDB end
-- IXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXI
if upper(@typ) = 'IX' begin declare cur_DrpIndDB cursor local for
select name,object_name(id) from sysindexes where indid > 0 and indid < 255
and (status & 64) = 0 and (status & 2048) = 0
and exists(select '*' from sysobjects
where id = sysindexes.id
and xtype = 'U' and status > 0
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
and name = isnull(@obj,name) order by name
open cur_DrpIndDB
fetch next from cur_DrpIndDB into @OB,@TTT
while @@FETCH_STATUS >= 0 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysindexes'+
' where name = '''+@OB+''' and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsIndexed'') = 1)')
if exists(select '*' from sysobjects where name = @OB)
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB) else
insert into @ResTab values ('drop index '+@TTT+'.'+@OB)
insert into @ResTab values ('go')
fetch next from cur_DrpIndDB into @OB,@TTT end
close cur_DrpIndDB deallocate cur_DrpIndDB end
-- FKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKF
if upper(@typ) = 'FK' begin declare cur_DrpForDB cursor local for
select a.name,b.name from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'F' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_DrpForDB
fetch next from cur_DrpForDB into @OB,@TTT
while @@FETCH_STATUS >= 0 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsForeignKey'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go')
fetch next from cur_DrpForDB into @OB,@TTT end
close cur_DrpForDB deallocate cur_DrpForDB end
-- CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
if upper(@typ) = 'CC' begin declare cur_DrpCcdDB cursor local for
select a.name,b.name from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'C' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_DrpCcdDB
fetch next from cur_DrpCcdDB into @OB,@TTT
while @@FETCH_STATUS >= 0 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsCheckCnst'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go')
fetch next from cur_DrpCcdDB into @OB,@TTT end
close cur_DrpCcdDB deallocate cur_DrpCcdDB end
-- DFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFD
if upper(@typ) = 'DF' begin declare cur_DrpDfdDB cursor local for
select a.name,b.name from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'D' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_DrpDfdDB
fetch next from cur_DrpDfdDB into @OB,@TTT
while @@FETCH_STATUS >= 0 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsDefaultCnst'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go')
fetch next from cur_DrpDfdDB into @OB,@TTT end
close cur_DrpDfdDB deallocate cur_DrpDfdDB end
-- ===========================================================================
if @Y_NL = 0 select t from @ResTab order by i
else if @Y_NL = 1 select str(i,10,0)+'.# ' i,t from @ResTab order by i
else select i,t from @ResTab order by i for xml auto,xmldata
if @@ROWCOUNT < 5 return 3 return 0 end
-- __CREATE_CREATE_CREATE_CREATE_CREATE_CREATE_CREATE_CREATE_CREATE_CREATE___>
-- Создание об'ектов
if @Y_DR = 0 select @S='-- Create Objects. (c) "PolySystem" Kyiv'
else select @S='-- Drop & Create Objects. (c) "PolySystem" Kyiv'
select @S=@S+space(15)+convert(varchar(10),getdate(),104)+' '+
convert(varchar(5),getdate(),8)
insert into @ResTab values (@S)
if @Y_DR = 0 select @S='-- ====================================='
else select @S='-- ============================================'
select @S=@S+space(15)+'================'
insert into @ResTab values (@S) insert into @ResTab values ('go')
insert into @ResTab values ('')
-- TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT
if upper(@typ) = 'T' begin declare cur_CreaTablDB cursor local for
select name,id from sysobjects where xtype = 'U' and status >= 0
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = isnull(@obj,name) order by name
create table #ResTab (i int NOT NULL identity,
t varchar(300) collate database_default NULL)
open cur_CreaTablDB fetch next from cur_CreaTablDB into @OB,@ID
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin if @Y_DR = 1 begin
-- REFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREF
declare cur_DrpDepTablDB cursor local for select a.constid,b.parent_obj
from sysreferences a,sysobjects b where a.rkeyid = object_id(@OB)
and a.constid = b.id and b.xtype = 'F'
open cur_DrpDepTablDB fetch next from cur_DrpDepTablDB into @r,@p
while @@FETCH_STATUS >= 0 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+object_name(@r)+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsForeignKey'') = 1)')
insert into @ResTab values ('alter table '+object_name(@p)+
' drop constraint '+object_name(@r))
insert into @ResTab values ('go')
fetch next from cur_DrpDepTablDB into @r,@p end
close cur_DrpDepTablDB deallocate cur_DrpDepTablDB
-- REFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREFTABFKREF
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsUserTable'') = 1)'+
' drop table '+@OB)
insert into @ResTab values ('go') end end
insert into #ResTab(t) exec SP_StrDB @T=@OB
insert into #ResTab(t) exec SP_PutPropertyObjDB @f=5,@o=@OB,@m=NULL
insert into #ResTab(t) exec SP_PutPropertyObjDB @f=6,@o=@OB,@m=NULL
delete #ResTab where datalength(ltrim(rtrim(isnull(t,'')))) = 0
or substring(ltrim(t),1,2) = '--'
insert into @ResTab(t) select t from #ResTab order by i drop table #ResTab
fetch next from cur_CreaTablDB into @OB,@ID end
close cur_CreaTablDB deallocate cur_CreaTablDB end
-- VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
if upper(@typ) = 'V' begin declare cur_CreaViewDB cursor local for
select id,name from sysobjects where xtype = 'V' and status >= 0
and OBJECTPROPERTY(id, N'IsView') = 1
and name =isnull(@obj,name) order by name
open cur_CreaViewDB
fetch next from cur_CreaViewDB into @ID,@OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsView'') = 1)'+
' drop view '+@OB)
insert into @ResTab values ('go') end
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = 1
select @r=charindex('create',@TXT),
@TXT=substring(@TXT,@r,datalength(@TXT)-@r+1),@p=datalength(@TXT)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)

if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end
select @COLID=2 while 1 = 1 begin
select @TXT=NULL,@r=datalength(@S) set rowcount 1
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = @COLID set rowcount 0
select @COLID = @COLID+1,@p=datalength(isnull(@TXT,''))
if @p = 0 begin if @r > 0 insert into @ResTab(t) values(@S) break end
if @p = 0 break select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p)
select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p) end
if @r > 0 insert into @ResTab(t) values(@S) select @S='',@r=0
if @p = 0 break end
select @r=charindex(char(13)+char(10),@TXT),
@S=@S+case when @r = 1 then '' else substring(@TXT,1,@r-1) end,
@p=@p-@r-1,@TXT=substring(@TXT,@r+2,@p)
insert into @ResTab(t) values(@S)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)
if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end end
insert into @ResTab values ('go')
insert into @ResTab values ('') insert into @ResTab values ('') end
fetch next from cur_CreaViewDB into @ID,@OB end
deallocate cur_CreaViewDB end
-- PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP
if upper(@typ) = 'P' begin declare cur_CreaProcDB cursor local for
select id,name from sysobjects where xtype = 'P' and status >= 0
and OBJECTPROPERTY(id, N'IsProcedure') = 1
and name =isnull(@obj,name) order by name
open cur_CreaProcDB
fetch next from cur_CreaProcDB into @ID,@OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsProcedure'') = 1)'+
' drop procedure '+@OB)
insert into @ResTab values ('go') end
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = 1
select @r=charindex('create',@TXT),
@TXT=substring(@TXT,@r,datalength(@TXT)-@r+1),@p=datalength(@TXT)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)
if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end
select @COLID=2 while 1 = 1 begin
select @TXT=NULL,@r=datalength(@S) set rowcount 1
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = @COLID set rowcount 0
select @COLID = @COLID+1,@p=datalength(isnull(@TXT,''))
if @p = 0 begin if @r > 0 insert into @ResTab(t) values(@S) break end
if @p = 0 break select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p)
select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p) end
if @r > 0 insert into @ResTab(t) values(@S) select @S='',@r=0
if @p = 0 break end
select @r=charindex(char(13)+char(10),@TXT),
@S=@S+case when @r = 1 then '' else substring(@TXT,1,@r-1) end,
@p=@p-@r-1,@TXT=substring(@TXT,@r+2,@p)
insert into @ResTab(t) values(@S)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)
if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end end
insert into @ResTab values ('go')
insert into @ResTab values ('') insert into @ResTab values ('') end
fetch next from cur_CreaProcDB into @ID,@OB end
deallocate cur_CreaProcDB end
-- FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
if upper(@typ) = 'F' begin declare cur_CreaFuncDB cursor local for
select id,name from sysobjects where status >= 0
and (xtype ='FN' or xtype ='IF' or xtype ='TF')
and (OBJECTPROPERTY(id, N'IsScalarFunction') = 1
or OBJECTPROPERTY(id, N'IsInlineFunction') = 1
or OBJECTPROPERTY(id, N'IsTableFunction') = 1)
and name = isnull(@obj,name) order by name
open cur_CreaFuncDB
fetch next from cur_CreaFuncDB into @ID,@OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values
('(OBJECTPROPERTY(id, N''IsScalarFunction'') = 1 or'+
' OBJECTPROPERTY(id, N''IsInlineFunction'') = 1 or')
insert into @ResTab values
(' OBJECTPROPERTY(id, N''IsTableFunction'') = 1))'+
' drop function '+@OB)
insert into @ResTab values ('go') end
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = 1
select @r=charindex('create',@TXT),
@TXT=substring(@TXT,@r,datalength(@TXT)-@r+1),@p=datalength(@TXT)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)
if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end
select @COLID=2 while 1 = 1 begin
select @TXT=NULL,@r=datalength(@S) set rowcount 1
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = @COLID set rowcount 0
select @COLID = @COLID+1,@p=datalength(isnull(@TXT,''))
if @p = 0 begin if @r > 0 insert into @ResTab(t) values(@S) break end
if @p = 0 break select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p)
select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p) end
if @r > 0 insert into @ResTab(t) values(@S) select @S='',@r=0
if @p = 0 break end
select @r=charindex(char(13)+char(10),@TXT),
@S=@S+case when @r = 1 then '' else substring(@TXT,1,@r-1) end,
@p=@p-@r-1,@TXT=substring(@TXT,@r+2,@p)
insert into @ResTab(t) values(@S)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)
if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end end
insert into @ResTab values ('go')
insert into @ResTab values ('') insert into @ResTab values ('') end
fetch next from cur_CreaFuncDB into @ID,@OB end
deallocate cur_CreaFuncDB end
-- TRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRTRT
if upper(@typ) = 'TR' begin declare cur_CreaTrigDB cursor local for
select a.id,a.name from sysobjects a, sysobjects b where a.parent_obj = b.id
and a.xtype = 'TR' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(a.id, N'IsTrigger') = 1
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_CreaTrigDB
fetch next from cur_CreaTrigDB into @ID,@OB
while (@@fetch_status != -1) begin
if (@@fetch_status != -2) begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsTrigger'') = 1)'+
' drop trigger '+@OB)
insert into @ResTab values ('go') end
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = 1
select @r=charindex('create',@TXT),
@TXT=substring(@TXT,@r,datalength(@TXT)-@r+1),@p=datalength(@TXT)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)
if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end
select @COLID=2 while 1 = 1 begin
select @TXT=NULL,@r=datalength(@S) set rowcount 1
select @TXT=convert(varchar(4000),text) from syscomments
where id = @ID and colid = @COLID set rowcount 0
select @COLID = @COLID+1,@p=datalength(isnull(@TXT,''))
if @p = 0 begin if @r > 0 insert into @ResTab(t) values(@S) break end
if @p = 0 break select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p)
select @x=substring(@TXT,1,1)
if @x = char(13) or @x = char(10) begin
select @p=@p-1
if @p > 0 select @TXT=substring(@TXT,2,@p) end
if @r > 0 insert into @ResTab(t) values(@S) select @S='',@r=0
if @p = 0 break end
select @r=charindex(char(13)+char(10),@TXT),
@S=@S+case when @r = 1 then '' else substring(@TXT,1,@r-1) end,
@p=@p-@r-1,@TXT=substring(@TXT,@r+2,@p)
insert into @ResTab(t) values(@S)
while 1 = 1 begin
select @r=charindex(char(13),@TXT) if @r = 0 select @r=charindex(char(10),@TXT)
select @S=case when @r = 0 then @TXT else substring(@TXT,1,@r-1) end
if @r = 0 break insert into @ResTab(t) values(@S)
select @p=@p-@r if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p),@r=charindex(char(10),@TXT)
if @r = 1 begin select @p=@p-@r
if @p=0 begin select @S='' break end
select @TXT=substring(@TXT,@r+1,@p) end end end
insert into @ResTab values ('go')
insert into @ResTab values ('') insert into @ResTab values ('') end
fetch next from cur_CreaTrigDB into @ID,@OB end
deallocate cur_CreaTrigDB end
-- PKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKPKP
if upper(@typ) = 'PK' begin declare cur_CreaPkeDB cursor local for
select name,object_name(id),status,indid from sysindexes where indid > 0
and indid < 255
and (status & 64) = 0 and (status & 2048) != 0
and exists(select '*' from sysobjects
where id = sysindexes.id
and xtype = 'U' and status > 0
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
and name = isnull(@obj,name) order by name
open cur_CreaPkeDB
fetch next from cur_CreaPkeDB into @OB,@TTT,@p,@r
while @@FETCH_STATUS >= 0 begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsPrimaryKey'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go') end
select @TXT='('+index_col(@TTT,@r,1), @ID=2, @S=index_col(@TTT,@r,2)
while (@S is not null )
select @TXT=@TXT+','+@S, @ID=@ID+1, @S = index_col(@TTT,@r,@ID)
insert into @ResTab values ('alter table '+@TTT+ ' with nocheck add')
insert into @ResTab values (' constraint '+@OB+ ' primary key'+
case when (@P&16) != 0 then ' clustered' else ' nonclustered' end)
insert into @ResTab values (' '+@TXT+')')
insert into @ResTab values ('go')
fetch next from cur_CreaPkeDB into @OB,@TTT,@p,@r end
close cur_CreaPkeDB deallocate cur_CreaPkeDB end
-- IXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXIXI
if upper(@typ) = 'IX' begin declare cur_CreaIndDB cursor local for
select name,object_name(id),status,indid from sysindexes where indid > 0
and indid < 255
and (status & 64) = 0 and (status & 2048) = 0
and exists(select '*' from sysobjects
where id = sysindexes.id
and xtype = 'U' and status > 0
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
and name = isnull(@obj,name) order by name
open cur_CreaIndDB
fetch next from cur_CreaIndDB into @OB,@TTT,@p,@r
while @@FETCH_STATUS >= 0 begin
select @TXT='('+index_col(@TTT,@r,1), @ID=2, @S=index_col(@TTT,@r,2)
while (@S is not null )
select @TXT=@TXT+','+@S, @ID=@ID+1, @S = index_col(@TTT,@r,@ID)
if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysindexes'+
' where name = '''+@OB+''' and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsIndexed'') = 1)') end
if exists(select '*' from sysobjects where name = @OB) begin
if @Y_DR = 1 begin
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go') end
insert into @ResTab values ('alter table '+@TTT+ ' with nocheck add')
insert into @ResTab values (' constraint '+@OB+' unique'+
case when (@p&16) != 0 then ' clustered' else ' nonclustered' end)
insert into @ResTab values (' '+@TXT+')') end else begin
if @Y_DR = 1 begin
insert into @ResTab values ('drop index '+@TTT+'.'+@OB)
insert into @ResTab values ('go') end
if (@p&4096+@p&2) != 0 begin
insert into @ResTab values ('create unique'+
case when (@p&16) != 0 then ' clustered' else ' nonclustered' end+
' index '+@OB+ ' on '+@TTT) end else begin
insert into @ResTab values ('create'+
case when (@p&16) != 0 then ' clustered' else ' nonclustered' end+
' index '+@OB+ ' on '+@TTT) end
insert into @ResTab values (' '+@TXT+')') end
insert into @ResTab values ('go')
fetch next from cur_CreaIndDB into @OB,@TTT,@p,@r end
close cur_CreaIndDB deallocate cur_CreaIndDB end
-- FKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKFKF
if upper(@typ) = 'FK' begin declare cur_CreaForDB cursor local for
select a.name,b.name,a.id from sysobjects a, sysobjects b
where a.parent_obj = b.id
and a.xtype = 'F' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_CreaForDB
fetch next from cur_CreaForDB into @OB,@TTT,@r
while @@FETCH_STATUS >= 0 begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsForeignKey'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go') end
select @j=fkeyid, @p=rkeyid from sysreferences where constid = @r
declare cur_SelFkeyTabDB cursor local for select fkey, rkey
from sysforeignkeys where constid = @r
open cur_SelFkeyTabDB
fetch next from cur_SelFkeyTabDB into @IDN, @IDF
select @TXT=col_name(@j,@IDN), @S=col_name(@p,@IDF)
fetch next from cur_SelFkeyTabDB into @IDN, @IDF
while @@fetch_status >= 0 begin
select @TXT=@TXT+','+col_name(@j,@IDN), @S=@S+','+col_name(@p,@IDF)
fetch next from cur_SelFkeyTabDB into @IDN,@IDF end
close cur_SelFkeyTabDB deallocate cur_SelFkeyTabDB
insert into @ResTab values ('alter table '+@TTT+ ' with nocheck add')
insert into @ResTab values (' constraint '+@OB+' foreign key')
insert into @ResTab values (' ('+@TXT+')')
insert into @ResTab values (' references '+object_name(@p))
select @p=ObjectProperty(@r,'CnstIsDeleteCascade'),
@j=ObjectProperty(@r,'CnstIsUpdateCascade'),
@TXT=case when @p = 1 and @j = 1 then
'on delete cascade on update cascade'
when @p = 1 then 'on delete cascade'
when @j = 1 then 'on update cascade'
else NULL end
if @TXT is not NULL begin insert into @ResTab values (' ('+@S+')')
insert into @ResTab values (' '+@TXT) end
else begin insert into @ResTab values (' ('+@S+')') end
insert into @ResTab values ('go')
fetch next from cur_CreaForDB into @OB,@TTT,@r end
close cur_CreaForDB deallocate cur_CreaForDB end
-- CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
if upper(@typ) = 'CC' begin declare cur_CreaCcdDB cursor local for
select a.name,b.name,a.id from sysobjects a, sysobjects b
where a.parent_obj = b.id
and a.xtype = 'C' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_CreaCcdDB
fetch next from cur_CreaCcdDB into @OB,@TTT,@r
while @@FETCH_STATUS >= 0 begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsCheckCnst'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go') end
select @TXT = convert(varchar(4000),text) from syscomments
where id = @r and colid = 1
insert into @ResTab values ('alter table '+@TTT+ ' with nocheck add')
insert into @ResTab values (' constraint '+@OB)
insert into @ResTab values (' check '+@TXT)
insert into @ResTab values ('go')
fetch next from cur_CreaCcdDB into @OB,@TTT,@r end
close cur_CreaCcdDB deallocate cur_CreaCcdDB end
-- DFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFD
if upper(@typ) = 'DF' begin declare cur_CreaDfdDB cursor local for
select a.name,b.name,a.id from sysobjects a, sysobjects b
where a.parent_obj = b.id
and a.xtype = 'D' and b.xtype = 'U' and b.status >= 0
and OBJECTPROPERTY(b.id, N'IsUserTable') = 1
and a.name = isnull(@obj,a.name) order by a.name
open cur_CreaDfdDB
fetch next from cur_CreaDfdDB into @OB,@TTT,@r
while @@FETCH_STATUS >= 0 begin if @Y_DR = 1 begin
insert into @ResTab values ('if exists (select ''*'' from dbo.sysobjects'+
' where id = object_id(N'''+@OB+''') and')
insert into @ResTab values ('OBJECTPROPERTY(id, N''IsDefaultCnst'') = 1)')
insert into @ResTab values ('alter table '+@TTT+' drop constraint '+@OB)
insert into @ResTab values ('go') end
select @TXT = convert(varchar(4000),text) from syscomments
where id = @r and colid = 1
select @S=name from syscolumns where id = object_id(@TTT) and cdefault = @r
insert into @ResTab values ('alter table '+@TTT+ ' with nocheck add')
insert into @ResTab values (' constraint '+@OB)
insert into @ResTab values (' default '+@TXT+' for '+@S)
insert into @ResTab values ('go')
fetch next from cur_CreaDfdDB into @OB,@TTT,@r end
close cur_CreaDfdDB deallocate cur_CreaDfdDB end
-- ===========================================================================
if @Y_NL = 0 select t from @ResTab order by i
else if @Y_NL = 1 select str(i,10,0)+'.# ' i,t from @ResTab order by i
else select i,t from @ResTab order by i for xml auto,xmldata
if @@ROWCOUNT < 5 return 3 return 0
-- #### (c) "PolySystem" 26.09.99 #########################################

-- Вызов:
exec SP_GenScr 'p','cd'
За все время исп. сбора неверного текста не наблюдалось.
естественно проц. должны быть "не засекреченными"