USE [……]
GO
/****** Object: StoredProcedure [dbo].[p_init_busno] Script Date: 08/18/2022 16:14:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*---------------------------------------------------------------
功能：得到創建數據庫代碼，和初始化新業務機構的語句
gthlu 2007-06-04
---------------------------------------------------------------*/
CREATE proc [dbo].[p_init_busno](@busno varchar(10),@userid varchar(10))
as
declare @sql varchar(1000)
declare @maxorderid int,@orderid int,@zoneno varchar(10)
declare @procname nvarchar(100)
--使有替換存儲過程設置查詢是否有替換存儲過程 YB 2020-5-13
set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID))
If @procname > ''
Begin
set @procname = 'exec ' + @procname + ' @busno,@userid'
execute sp_executesql @procname, N'@busno varchar(10),@userid varchar(10)', @busno,@userid
return
End
if not exists(select * from c_org_busi where busno = @busno )
begin
raiserror("指定的業務機構編碼沒有在“業務機構代碼”里設置好！",16,1)
return
end
if not exists(select * from c_user_org where org_tran_code = @busno and userid = @userid and status = 1)
begin
raiserror("請先將您自己的權限在“用戶對應機構”中設置為能使用目標業務機構！",16,1)
return
end
if not exists(select * from c_org_busi b,c_zone c where b.busno=c.busno and b.busno=@busno)
begin
raiserror("指定的業務機構編碼沒有在“庫區設置”里設置好庫區！",16,1)
return
end
if not exists(select * from c_org_busi b,c_zone c,c_stall d where b.busno=c.busno
and c.zoneno=d.zoneno and b.busno=@busno and d.stalltype='11')
begin
raiserror("指定的業務機構編碼沒有在“貨位設置”里設置好【正常貨位】！",16,1)
return
end
-- 2966 如果啟用門店驗收確認功能，還需要檢查這兩個貨位
if exists(select * from c_sys_ini where ini='2966' and para='1')
begin
if not exists(select * from c_org_busi b,c_zone c,c_stall d where b.busno=c.busno
and c.zoneno=d.zoneno and b.busno=@busno and d.stalltype='21')
begin
raiserror("指定的業務機構編碼沒有在“貨位設置”里設置好【待處理貨位】！",16,1)
return
end
if not exists(select * from c_org_busi b,c_zone c,c_stall d where b.busno=c.busno
and c.zoneno=d.zoneno and b.busno=@busno and d.stalltype='22')
begin
raiserror("指定的業務機構編碼沒有在“貨位設置”里設置好【不合格貨位】！",16,1)
return
end
end
create table #data(sql varchar(8000),orderid int identity(1,1),orderid_temp int null)
--免調試SQL系統參數 by liwenlong 20140521
insert into #data ( sql ) select 'SET ANSI_NULLS ON'
insert into #data ( sql ) select 'go'
insert into #data ( sql ) select 'SET QUOTED_IDENTIFIER OFF'
insert into #data ( sql ) select 'go'
--免調SQL試系統參數 結束 by liwenlong 20140521
insert into #data(sql)
exec p_get_usertable
insert into #data(sql)
select 'go'
insert into #data(sql)
exec p_get_usertable_primary
insert into #data(sql)
select 'go'
insert into #data(sql)
exec p_get_usertable_index
insert into #data(sql)
select 'go'
insert into #data(sql)
exec p_get_objtext
insert into #data(sql)
select 'go'
select @orderid = max(orderid) from #data
insert into #data(sql)
select "ALTER TABLE c_sys_ini disable trigger all"
insert into #data(sql)
exec p_get_select 'c_sys_ini','ini<>''1006'' and ini<>''1007'' and ini<>''1008'' and ini<>''1009'''
insert into #data(sql)
select "ALTER TABLE c_sys_ini enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "update c_sys_ini set para = '" + @busno + "' where ini = '1001'"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_dddw disable trigger all"
insert into #data(sql)
exec p_get_select 'c_dddw',''
insert into #data(sql)
select "ALTER TABLE c_dddw enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_bill disable trigger all"
insert into #data(sql)
exec p_get_select 'c_bill ',''
insert into #data(sql)
select "ALTER TABLE c_bill enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_columns disable trigger all"
insert into #data(sql)
exec p_get_select 'c_columns ',''
insert into #data(sql)
select "ALTER TABLE c_columns enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_dyreport disable trigger all"
insert into #data(sql)
exec p_get_select 'c_dyreport ',''
insert into #data(sql)
select "ALTER TABLE c_dyreport enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_function disable trigger all"
insert into #data(sql)
exec p_get_select 'c_function',''
insert into #data(sql)
select "ALTER TABLE c_function enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_function_ext disable trigger all"
insert into #data(sql)
exec p_get_select 'c_function_ext',''
insert into #data(sql)
select "ALTER TABLE c_function_ext enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_sale_printer disable trigger all"
insert into #data(sql)
exec p_get_select 'c_sale_printer',''
insert into #data(sql)
select "ALTER TABLE c_sale_printer enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_help disable trigger all"
insert into #data(sql)
exec p_get_select 'c_help',''
insert into #data(sql)
select "ALTER TABLE c_help enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_objects disable trigger all"
insert into #data(sql)
exec p_get_select 'c_objects',''
insert into #data(sql)
select "ALTER TABLE c_objects enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_period disable trigger all"
insert into #data(sql)
exec p_get_select 'c_period',''
insert into #data(sql)
select "ALTER TABLE c_period enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_print_m disable trigger all"
insert into #data(sql)
exec p_get_select 'c_print_m',''
insert into #data(sql)
select "ALTER TABLE c_print_m enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_print_c disable trigger all"
insert into #data(sql)
exec p_get_select 'c_print_c',''
insert into #data(sql)
select "ALTER TABLE c_print_c enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_proc_set disable trigger all"
insert into #data(sql)
exec p_get_select 'c_proc_set',''
insert into #data(sql)
select "ALTER TABLE c_proc_set enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_relation disable trigger all"
insert into #data(sql)
exec p_get_select 'c_relation',''
insert into #data(sql)
select "ALTER TABLE c_relation enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_retrieve disable trigger all"
insert into #data(sql)
exec p_get_select 'c_retrieve',''
insert into #data(sql)
select "ALTER TABLE c_retrieve enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE t_tranitem disable trigger all"
insert into #data(sql)
exec p_get_select 't_tranitem',''
insert into #data(sql)
select "ALTER TABLE t_tranitem enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_stroke disable trigger all"
insert into #data(sql)
exec p_get_select 'c_stroke',''
insert into #data(sql)
select "ALTER TABLE c_stroke enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_py disable trigger all"
insert into #data(sql)
exec p_get_select 'c_py',''
insert into #data(sql)
select "ALTER TABLE c_py enable trigger all"
insert into #data(sql)
select 'go'
select @sql =
"insert into #data(sql) exec p_get_select 'c_org_tran'," + """where org_tran_code = '" + @busno + "'"""
exec( @sql)
select @sql =
"insert into #data(sql) exec p_get_select 'c_org_tran'," + """where org_tran_code = '" + rtrim(dbo.f_sys_ini('1001')) + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_org_busi disable trigger all"
select @sql =
"insert into #data(sql) exec p_get_select 'c_org_busi'," + """where busno = '" + @busno + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_org_busi enable trigger all"
insert into #data(sql)
select "ALTER TABLE c_user disable trigger all"
select @sql =
"insert into #data(sql) exec p_get_select 'c_user'," + """where userid = '" + @userid + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_user enable trigger all"
insert into #data(sql)
select "ALTER TABLE c_user_func disable trigger all"
select @sql =
"insert into #data(sql) exec p_get_select 'c_user_func'," + """where userid = '" + @userid + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_user_func enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_user_func_ext disable trigger all"
select @sql =
"insert into #data(sql) exec p_get_select 'c_user_func_ext'," + """where userid = '" + @userid + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_user_func_ext enable trigger all"
insert into #data(sql)
select "ALTER TABLE c_user_func_shop disable trigger all"
select @sql =
"insert into #data(sql) exec p_get_select 'c_user_func_shop'," + """where userid = '" + @userid + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_user_func_shop enable trigger all"
insert into #data(sql)
select 'go'
insert into #data(sql)
select "ALTER TABLE c_user_org disable trigger all"
select @sql =
"insert into #data(sql) exec p_get_select 'c_user_org'," + """where userid = '" + @userid + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_user_org enable trigger all"
insert into #data(sql)
select "ALTER TABLE c_user_dept disable trigger all"
select @sql =
"insert into #data(sql) exec p_get_select 'c_user_dept'," + """where userid = '" + @userid + "'"""
exec( @sql)
insert into #data(sql)
select "ALTER TABLE c_user_dept enable trigger all"
update #data set orderid_temp = orderid
select @maxorderid = max(orderid) from #data
while @orderid < @maxorderid
begin
select @orderid = @orderid + 10
insert into #data(sql,orderid_temp) select 'go',@orderid
end
insert into #data(sql)
select 'go'
--新店所有的老郵件附件不傳下去
update t_transet_c set maxserialno = convert(bigint,@@dbts) where tranid = '004' and tranorg_obj = @busno
select sql from #data order by orderid_temp
drop table #data
GO
其中，存儲過程 p_get_usertable 代碼如下：
/*----------------------------------------------------------------------
功能：取得一個數據庫里所有用戶表的建表代碼
by gthlu 2005-10-15
-----------------------------------------------------------------------*/
CREATE proc [dbo].[p_get_usertable]
as
declare @sql varchar(4000)
declare @tabname varchar(100)
declare @colname varchar(80),@cdefault int,@domain int,@xusertype smallint,
@prec smallint,@scale int,@isnullable int,@autoval int ,@ctext_compute varchar(8000)
declare @procname nvarchar(100)
declare @typeName sysname,@xscale tinyint
--使有替換存儲過程設置查詢是否有替換存儲過程 YB 2020-5-13
set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID))
If @procname > ''
Begin
set @procname = 'exec ' + @procname
execute sp_executesql @procname
return
End
create table #tabtext(txt varchar(4000),orderid int identity(1,1))
declare tabname_cursor cursor for select a.name from sysobjects a,sysusers b
where a.xtype = 'u' and b.uid = a.uid and b.name = 'dbo' --and a.name = 't_ol_order_m'
open tabname_cursor
fetch next from tabname_cursor into @tabname
while @@fetch_status >= 0
begin
select @sql = 'if object_id(''' + @tabname + ''') is null ' + 'create table [' + @tabname + ']('
--修正sql2005下 autoval 不能正確判斷自增長字段--by黃才旺 2008-12-17
declare column_cursor cursor for
select syscolumns.name,syscolumns.cdefault,syscolumns.domain,syscolumns.xusertype,syscolumns.prec,c.name as typeName,
syscolumns.scale,syscolumns.isnullable ,COLUMNPROPERTY(syscolumns.id,syscolumns.name,'IsIdentity') ,b.text,c.xscale
from syscolumns left join syscomments b
on syscolumns.ID=b.ID AND syscolumns.Colid=b.number
join systypes c on syscolumns.xusertype = c.xusertype
where syscolumns.id = object_id(@tabname)
order by syscolumns.colid
open column_cursor
fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@typeName,
@scale,@isnullable ,@autoval ,@ctext_compute,@xscale
while @@fetch_status >= 0
begin
if len(@sql)>3600
begin
insert into #tabtext(txt) select @sql
set @sql=''
end
if len(isnull(@ctext_compute,''))>0
select @sql=@sql + '[' + @colname + '] AS ' + @ctext_compute + ','
else
begin
select @sql = @sql + '[' + @colname + ']' + ' ' + @typeName
+ case when @prec is null or @prec = 0 or @prec = (select prec from systypes where xusertype = @xusertype and
(status <> 2 and name not in ('binary','char','nchar','nvarchar','varbinary','varchar') )) then ' '
else '(' + cast(case when @prec = -1 then case when @typeName in ('nchar','nvarchar') then 4000 else 8000 end else @prec end as varchar(10))
+ case when @scale is null or @scale = @xscale then ')'
else ',' + cast(@scale as varchar(10)) + ')' end end
+ case when @cdefault is null or @cdefault = 0 then ''
else (select case when charindex('create',text) = 0 then ' default ' + text else '' end from syscomments where id = @cdefault) end
+ case @isnullable when 0 then ' not null'
when 1 then ' null'
else ' ' end
+ case when @autoval <> 1 then ','
else ' identity(' + convert(varchar,ident_seed(@tabname)) + ',' + convert(varchar,ident_incr(@tabname)) + '),' end
--select @sql = @sql + '[' + @colname + ']' + (select ' ' + name from systypes where xusertype = @xusertype)
-- + case when @prec is null or @prec = 0 or @prec = (select prec from systypes where xusertype = @xusertype and
-- (status <> 2 and name not in ('binary','char','nchar','nvarchar','varbinary','varchar') )) then ' '
-- else '(' + cast(case when @prec = -1 then 8000 else @prec end as varchar(10))
-- + case when @scale is null or @scale = (select scale from systypes where xusertype = @xusertype) then ')'
-- else ',' + cast(@scale as varchar(10)) + ')' end end
-- + case when @cdefault is null or @cdefault = 0 then ''
-- else (select case when charindex('create',text) = 0 then ' default ' + text else '' end from syscomments where id = @cdefault) end
-- + case @isnullable when 0 then ' not null'
-- when 1 then ' null'
-- else ' ' end
-- + case when @autoval <> 1 then ','
-- else ' identity(' + convert(varchar,ident_seed(@tabname)) + ',' + convert(varchar,ident_incr(@tabname)) + '),' end
end
fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@typeName,
@scale,@isnullable,@autoval ,@ctext_compute,@xscale
end
close column_cursor
deallocate column_cursor
fetch next from tabname_cursor into @tabname
select @sql = left(@sql,len(rtrim(@sql))-1) + ')'
insert into #tabtext(txt) select @sql
insert into #tabtext(txt) select 'go'
end
close tabname_cursor
deallocate tabname_cursor
--------------綁定列
insert into #tabtext(txt)
select txt = 'if object_id(''[' + name + ']'') is null exec(''' + replace(text,'''','''''') + ''')'
from sysobjects a,syscomments b
where a.id = b.id and a.xtype = 'D' and b.text like '%create%'
insert into #tabtext(txt) select 'go'
insert into #tabtext(txt)
select 'exec sp_bindefault ''[' + a.name + ']'',''[' + object_name(c.id) + '].[' + c.name + ']'''
from sysobjects a,syscomments b,syscolumns c
where a.id = b.id and a.xtype = 'D' and b.text like '%create%' and a.id = c.cdefault
select txt from #tabtext order by orderid
drop table #tabtext
GO
存儲過程 p_get_usertable_primary代碼如下：
/*----------------------------------------------------------------------
功能：得到當前數據庫里所有表的主鍵代碼
by gthlu 2007-06-02
-----------------------------------------------------------------------*/
CREATE proc [dbo].[p_get_usertable_primary]
as
DECLARE @table_id int
DECLARE @table_name nvarchar(255)
declare @colname varchar(255)
declare @pkname varchar(255)
declare @sql varchar(4000)
declare @procname nvarchar(100)
--使有替換存儲過程設置查詢是否有替換存儲過程 YB 2020-5-13
set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID))
If @procname > ''
Begin
set @procname = 'exec ' + @procname
execute sp_executesql @procname
return
End
--select @table_name = 'u_sale_c'
create table #pktext(txt varchar(4000),orderid int identity(1,1))
declare tabname_cursor cursor for
select a.name from sysobjects a,sysindexes b,sysusers c where a.type = 'u' and a.id = b.id and (b.status & 0x800) = 0x800 and a.uid = c.uid and c.name = 'dbo'
open tabname_cursor
fetch next from tabname_cursor into @table_name
while @@fetch_status >= 0
begin
SELECT @table_id = object_id(@table_name)
select colname = convert(sysname,c.name),o.name,pkname = convert(sysname,i.name),
rowid = case when c.name = index_col(@table_name, i.indid, 1) then convert (smallint,1)
when c.name = index_col(@table_name, i.indid, 2) then convert (smallint,2)
when c.name = index_col(@table_name, i.indid, 3) then convert (smallint,3)
when c.name = index_col(@table_name, i.indid, 4) then convert (smallint,4)
when c.name = index_col(@table_name, i.indid, 5) then convert (smallint,5)
when c.name = index_col(@table_name, i.indid, 6) then convert (smallint,6)
when c.name = index_col(@table_name, i.indid, 7) then convert (smallint,7)
when c.name = index_col(@table_name, i.indid, 8) then convert (smallint,8)
when c.name = index_col(@table_name, i.indid, 9) then convert (smallint,9)
when c.name = index_col(@table_name, i.indid, 10) then convert (smallint,10)
when c.name = index_col(@table_name, i.indid, 11) then convert (smallint,11)
when c.name = index_col(@table_name, i.indid, 12) then convert (smallint,12)
when c.name = index_col(@table_name, i.indid, 13) then convert (smallint,13)
when c.name = index_col(@table_name, i.indid, 14) then convert (smallint,14)
when c.name = index_col(@table_name, i.indid, 15) then convert (smallint,15)
when c.name = index_col(@table_name, i.indid, 16) then convert (smallint,16)
end
into #t
from sysindexes i, syscolumns c, sysobjects o
where o.id = @table_id and o.id = c.id and o.id = i.id and (i.status & 0x800) = 0x800
and (c.name = index_col (@table_name, i.indid, 1) or
c.name = index_col (@table_name, i.indid, 2) or
c.name = index_col (@table_name, i.indid, 3) or
c.name = index_col (@table_name, i.indid, 4) or
c.name = index_col (@table_name, i.indid, 5) or
c.name = index_col (@table_name, i.indid, 6) or
c.name = index_col (@table_name, i.indid, 7) or
c.name = index_col (@table_name, i.indid, 8) or
c.name = index_col (@table_name, i.indid, 9) or
c.name = index_col (@table_name, i.indid, 10) or
c.name = index_col (@table_name, i.indid, 11) or
c.name = index_col (@table_name, i.indid, 12) or
c.name = index_col (@table_name, i.indid, 13) or
c.name = index_col (@table_name, i.indid, 14) or
c.name = index_col (@table_name, i.indid, 15) or
c.name = index_col (@table_name, i.indid, 16)
)
select top 1 @pkname = pkname from #t
declare primary_cursor cursor for select colname from #t order by rowid
open primary_cursor
fetch next from primary_cursor into @colname
select @sql = "if not exists(select * from sysobjects where object_name(parent_obj) = '" + @table_name + "' and type = 'k') "
+ ' alter table ' + @table_name + ' add primary key ('
while @@fetch_status >= 0
begin
select @sql = @sql + @colname + ','
fetch next from primary_cursor into @colname
end
select @sql = left(@sql,len(rtrim(@sql))-1) + ')'
insert into #pktext(txt) select @sql
insert into #pktext(txt) select 'go'
close primary_cursor
deallocate primary_cursor
drop table #t
fetch next from tabname_cursor into @table_name
end
close tabname_cursor
deallocate tabname_cursor
select txt from #pktext order by orderid
drop table #pktext
GO
存儲過程 p_get_usertable_index代碼如下：
/*----------------------------------------------------------------------
功能：得到當前數據庫里所有索引創建代碼
by gthlu 2007-06-02
-----------------------------------------------------------------------*/
CREATE proc [dbo].[p_get_usertable_index]
as
DECLARE @table_id int
DECLARE @table_name nvarchar(255)
declare @index_name varchar(255)
declare @colname varchar(255)
declare @sql varchar(4000)
declare @status int
declare @procname nvarchar(100)
--使有替換存儲過程設置查詢是否有替換存儲過程 YB 2020-5-13
set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID))
If @procname > ''
Begin
set @procname = 'exec ' + @procname
execute sp_executesql @procname
return
End
create table #pktext(txt varchar(4000))
declare tabname_cursor cursor for
select a.name as table_name,b.name,b.status as index_name from sysobjects a,sysindexes b ,sysusers c
where a.xtype = 'u' and a.id = b.id and (b.status & 0x800) <> 0x800 and (b.status & 64)=0 and indid > 0 and indid < 255 and
c.name = 'dbo' and a.uid = c.uid
open tabname_cursor
fetch next from tabname_cursor into @table_name,@index_name,@status
while @@fetch_status >= 0
begin
SELECT @table_id = object_id(@table_name)
select colname = convert(sysname,c.name),o.name,@index_name as index_name,@status as status,
rowid = case when c.name = index_col(@table_name, i.indid, 1) then convert (smallint,1)
when c.name = index_col(@table_name, i.indid, 2) then convert (smallint,2)
when c.name = index_col(@table_name, i.indid, 3) then convert (smallint,3)
when c.name = index_col(@table_name, i.indid, 4) then convert (smallint,4)
when c.name = index_col(@table_name, i.indid, 5) then convert (smallint,5)
when c.name = index_col(@table_name, i.indid, 6) then convert (smallint,6)
when c.name = index_col(@table_name, i.indid, 7) then convert (smallint,7)
when c.name = index_col(@table_name, i.indid, 8) then convert (smallint,8)
when c.name = index_col(@table_name, i.indid, 9) then convert (smallint,9)
when c.name = index_col(@table_name, i.indid, 10) then convert (smallint,10)
when c.name = index_col(@table_name, i.indid, 11) then convert (smallint,11)
when c.name = index_col(@table_name, i.indid, 12) then convert (smallint,12)
when c.name = index_col(@table_name, i.indid, 13) then convert (smallint,13)
when c.name = index_col(@table_name, i.indid, 14) then convert (smallint,14)
when c.name = index_col(@table_name, i.indid, 15) then convert (smallint,15)
when c.name = index_col(@table_name, i.indid, 16) then convert (smallint,16)
end
into #t
from sysindexes i, syscolumns c, sysobjects o
where o.id = @table_id and o.id = c.id and o.id = i.id and (i.status & 0x800) <> 0x800 and i.name = @index_name and
indid > 0 and indid < 255 and (i.status & 64)=0
and (c.name = index_col (@table_name, i.indid, 1) or
c.name = index_col (@table_name, i.indid, 2) or
c.name = index_col (@table_name, i.indid, 3) or
c.name = index_col (@table_name, i.indid, 4) or
c.name = index_col (@table_name, i.indid, 5) or
c.name = index_col (@table_name, i.indid, 6) or
c.name = index_col (@table_name, i.indid, 7) or
c.name = index_col (@table_name, i.indid, 8) or
c.name = index_col (@table_name, i.indid, 9) or
c.name = index_col (@table_name, i.indid, 10) or
c.name = index_col (@table_name, i.indid, 11) or
c.name = index_col (@table_name, i.indid, 12) or
c.name = index_col (@table_name, i.indid, 13) or
c.name = index_col (@table_name, i.indid, 14) or
c.name = index_col (@table_name, i.indid, 15) or
c.name = index_col (@table_name, i.indid, 16)
)
select top 1 @index_name = index_name from #t
declare primary_cursor cursor for select colname from #t order by rowid
open primary_cursor
fetch next from primary_cursor into @colname
select @sql = "if exists (select * from sysindexes where name = '" + @index_name + "' and id = object_id('" + @table_name + "')) "
+ "drop index " + @table_name + "." + @index_name
+ ' CREATE '+ case WHEN (@status & 2)<>0 then 'UNIQUE ' ELSE '' END + 'INDEX ' + @index_name + ' ON ' + @table_name + '('
while @@fetch_status >= 0
begin
select @sql = @sql + @colname + ','
fetch next from primary_cursor into @colname
end
select @sql = left(@sql,len(rtrim(@sql))-1) + ')'
insert into #pktext select @sql
insert into #pktext select 'go'
close primary_cursor
deallocate primary_cursor
drop table #t
fetch next from tabname_cursor into @table_name,@index_name,@status
end
close tabname_cursor
deallocate tabname_cursor
select * from #pktext
drop table #pktext
GO
存儲過程 p_get_objtext代碼如下：
/*--------------------------------------------------------------------------------
功能：取數據庫里的全部存儲過程、觸發器和視圖,只適用于未加密的過程、觸發器和視圖
by gthlu 2005-10-14
修改：增加了取函數的代碼，解決了因代碼太長，導致截行問題，和視圖嵌套調用后運行報對象無效錯誤。 2007-06-13
---------------------------------------------------------------------------------*/
ALTER procedure [dbo].[p_get_objtext]
as
set nocount on
declare @BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int
,@DefinedLength int
,@SyscomText nvarchar(4000)
,@Line nvarchar(2000)
,@objid int
,@objname nvarchar(776)
,@objrowid int
,@maxorderid int
,@times int
declare @procname nvarchar(100)
--使有替換存儲過程設置查詢是否有替換存儲過程 YB 2020-5-14
set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID))
If @procname > ''
Begin
set @procname = 'exec ' + @procname
execute sp_executesql @procname
return
End
Select @DefinedLength = 2000
SELECT @BlankSpaceAdded = 0
if OBJECT_ID('tempdb..#objtext') is not null drop table #objtext
create table #objtext(txt nvarchar(2000),id int,LineId int)
if OBJECT_ID('tempdb..#objname') is not null drop table #objname
select distinct rtrim(object_name(a.id)) objname,case a.xtype
when 'V' then 400
when 'FN' then 600
when 'IF' then 300
when 'TF' then 500
when 'TR' then 100
when 'P' then 200
else 0
end orderid ,a.xtype,a.id
into #objname
from sysobjects a (nolock),sysusers b (nolock),syscomments c (nolock)
where a.xtype in ('FN','IF','TF','v','tr','p') and a.status >= 0 and a.uid = b.uid and b.name = 'dbo'
and c.encrypted<>1 and a.id = c.id
and left(lower(a.name),5) not in (lower('p_b2c'),lower('p_b2b'))
and a.name not like 'jc_p_%'
and not exists(select 1 from c_initsql_except where a.name = c_initsql_except.objectname)
--p_b2c,p_b2b去掉電商的存儲過程，因為里面用了些sql2000不支持的sql
-----------------------------------------------視圖被嵌套調用的先排序排在前面。
if OBJECT_ID('tempdb..#temp') is not null drop table #temp
create table #temp (objname varchar(200),id int,row int identity(1,1))
declare @id int
if OBJECT_ID('tempdb..#tmpmemts') is not null drop table #tmpmemts
select d.id,d.text into #tmpmemts from #objname c ,syscomments d (nolock) where c.id = d.id and c.xtype = 'V'
declare cursor_obj_v cursor for select rtrim(a.objname),a.id from #objname a where a.xtype = 'v' order by orderid
open cursor_obj_v
fetch next from cursor_obj_v into @objname,@id
while @@fetch_status >= 0
begin
if exists (select * from #tmpmemts where id <> @id and charindex(@objname ,text)>0)
begin
insert #temp (objname,id )select @objname,@id
end
fetch next from cursor_obj_v into @objname,@id
end
close cursor_obj_v
deallocate cursor_obj_v
--select * from #temp
while 1 = 1
begin
--select * into #order from #temp
select @times = isnull(@times,0) + 1 --為了防止死循環，當循環次數大于10次后，跳出。
if @times > 10 break
SELECT @maxorderid = max(orderid) FROM #objname where xtype = 'v'
update a
set orderid = orderid + row + abs(orderid - @maxorderid) from #objname a,#temp b
where a.objname = b.objname
if object_id('tempdb..#temp1') is not null drop table #temp1
select a.objname,identity(int,1,1) row
into #temp1
from #objname a ,#tmpmemts b ,#temp e
where a.id = b.id and a.xtype = 'v' and a.objname = e.objname and
exists(select * from #objname c ,#tmpmemts d ,#temp f
where c.id = d.id and c.xtype = 'V' and b.id <> d.id and c.objname = f.objname and charindex(a.objname ,text)>0)
if @@rowcount = 0 and @times > 10 break
truncate table #temp
set identity_insert #temp on
insert into #temp(objname,row) select objname,row from #temp1
drop table #temp1
end
if object_id('tempdb..#tmpmemts') is not null drop table #tmpmemts
if object_id('tempdb..#temp1') is not null drop table #temp1
---------------------------------------------------------------------
if OBJECT_ID('tempdb..#tmpmemts_all') is not null drop table #tmpmemts_all
select d.id,d.text,number,colid into #tmpmemts_all from #objname c ,syscomments d (nolock) where c.id = d.id
declare cursor_obj cursor for select objname,id from #objname order by orderid
open cursor_obj
fetch next from cursor_obj into @objname,@id
select @objid = 1
while @@fetch_status >= 0
begin
select @objrowid = isnull(@objrowid,0) + 1
if OBJECT_ID('tempdb..#CommentText') is not null drop table #CommentText
CREATE TABLE #CommentText
(LineId int,
Text nvarchar(2000) collate database_default)
insert into #commenttext select 0,'if object_id(''' + @objname + ''') is not null '
+ case (select xtype from sysobjects (nolock) where id = object_id(@objname))
when 'v' then 'drop view ' + @objname
when 'FN' then 'drop function ' + @objname
when 'IF' then 'drop function ' + @objname
when 'TF' then 'drop function ' + @objname
when 'tr' then 'drop trigger ' + @objname
when 'p' then 'drop proc ' + @objname
else ''
end
insert into #commenttext select 1,'go' union all select 2,char(13)
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM #tmpmemts_all WHERE id = @id order by number,colid FOR READ ONLY
SELECT @LFCR = 2
SELECT @LineId = 3
select @Line = ''
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
IF @CurrentPos != 0
BEGIN
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
BEGIN
IF @BasePos <= @TextLength
BEGIN
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
insert into #commenttext select @lineid+1,char(13) union all select @lineid + 2,'go' union all select @lineid + 3,char(13)
insert into #objtext select Text,@objrowid,lineid from #CommentText order by LineId
select @objid = @objid + 1
DROP TABLE #CommentText
fetch next from cursor_obj into @objname,@id
end
close cursor_obj
deallocate cursor_obj
select replace(txt,char(9),' ') from #objtext order by id desc,lineid
drop table #objtext
drop table #objname,#temp
if OBJECT_ID('tempdb..#tmpmemts_all') is not null drop table #tmpmemts_all
return (0)
浙公網安備 33010602011771號