create proc createproc
@database varchar(50)
,@tablename varchar(50)
as
set nocount on
declare @sql varchar(2000)
if isnull(@database,'') = ''
select @database=db_name()
if isnull(@tablename,'') = ''
begin
select '表名為空'
return
end
create table #A(A int)
set @sql='if not exists(select 1 from master..sysdatabases where name='''+@database+''') begin insert #A select ''1'' end '
--print @sql
exec(@sql)
if exists (select A from #A)
begin
select '數據庫'+@database+'不存在' as alert
drop table #A
return
end
declare @database1 varchar(40)
set @database1=@database
set @database='['+@database+'].dbo.'
set @sql='if not exists(select 1 from '+@database+'sysobjects where name='''+@tablename+''') begin insert #A select ''1'' end'
--print @sql
exec(@sql)
if exists(select A from #A)
begin
select '表'+@database+'['+@tablename+']不存在' as alert
drop table #A
return
end
-----------------------------------insert------------------------
set @sql='declare @a varchar(2000)
set @a=''Create Proc '+@database1+'Edit'+@tablename+char(10)+char(10)+'as'+char(10)+'if 1<>1 '+char(10)+'begin '+char(10)+'insert '+@database+@tablename+'('''
set @sql=@sql+'select @a=@a+'''+'''+b.name+'','' from '+@database+'sysobjects a,'+@database+'syscolumns b where a.name='''+@tablename+''' and a.id=b.id order by colorder
set @a=left(@a,len(@a)-1)+'')' +char(10)+'values (''
select @a=@a+''@''+b.name+'','' from '+@database+'sysobjects a,'+@database+'syscolumns b where a.name='''+@tablename+''' and a.id=b.id order by colorder
set @a=left(@a,len(@a)-1)+'')''+char(10)+''end''
print @a'
--print @sql
exec(@sql)
-----------------------------------update---------------------
set @sql='declare @a varchar(2000)
set @a=''else '+char(10)+'begin'+char(10)+'update '+@database+@tablename+' set '''
set @sql=@sql+'select @a=@a+''[''+b.name+'']''+'' = @''+b.name+char(10)+'','' from '+@database+'sysobjects a,'+@database+'syscolumns b where a.name='''+@tablename+''' and a.id=b.id order by colorder
set @a=left(@a,len(@a)-1)+'' where 1<>1''+char(10)+''end''
print @a'
--print @sql
exec(@sql)
------------------------寫存儲過程用---------------------
set @sql='select s.A,s.B,s.C,identity(int) as D,isnull(d.value,'''') E into #t from (select '',@''+b.name A,(case when c.name in(''datetime'',''bit'',''smallint'',''int'') then c.name when c.name in(''numeric'',''decimal'') then ''decimal(''+cast(b.xprec as varchar)+'',''+cast(b.xscale as varchar)+'')'' else c.name+'' (''+cast(b.length as varchar)+'')'' end) B,''----------------------------------------------------------'' C,a.id,b.colid
from '+@database+'sysobjects a,'+@database+'syscolumns b,'+@database+'systypes c
where a.name='''+@tablename+''' and a.id=b.id and b.xtype=c.xtype and c.name<>''sysname'' ) s left join
'+@database+'sysproperties d
on s.id=d.id and s.colid=d.smallid
order by s.colid
update #t set C=''-- ''+cast(D as varchar)+'' ''+cast(E as varchar)
select A,B,C from #t
drop table #t'
--print @sql
exec(@sql)
---------------------------寫cs代碼用---------------------------
set @sql='select ''arr.Add(''+s.A+'');'' cs,s.A,s.B,s.C,identity(int) as D,isnull(d.value,'''') E into #t from (select ''@''+b.name A,(case when c.name in(''datetime'',''bit'',''smallint'',''int'') then c.name when c.name in(''numeric'',''decimal'') then ''decimal(''+cast(b.xprec as varchar)+'',''+cast(b.xscale as varchar)+'')'' else c.name+'' (''+cast(b.length as varchar)+'')'' end) B,''----------------------------------------------------------'' C,a.id,b.colid
from '+@database+'sysobjects a,'+@database+'syscolumns b,'+@database+'systypes c
where a.name='''+@tablename+''' and a.id=b.id and b.xtype=c.xtype and c.name<>''sysname'' ) s left join
'+@database+'sysproperties d
on s.id=d.id and s.colid=d.smallid
order by s.colid
select * from (
select cs as [cs代碼用],''//''+A A,B,D,E as [表字段的descriptoin] from #t
union
select ''//arr.clear();'',''//存儲過程名'','''+@database1+'Edit'+@tablename+''','''',''CS代碼用''
union
select ''ArrayList arr = new ArrayList();'',''//存儲過程名'','''+@database1+'Edit'+@tablename+''',''-1'',''CS代碼用''
union
select ''SqlResult sr = Sys_Obj.Call_DataSet("存盤'','''',''100'',''''
) k order by d
drop table #t'
--print @sql
go