根据表名生成对该表操作的存储过程

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