根据表名生成对该表操作的存储过程
来源:互联网 发布:阿里云docker镜像站 编辑:程序博客网 时间:2024/05/21 06:56
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("'+@database1+'","'+@database1+'Edit'+@tablename+'",arr);'',''//存盤'','''',''100'',''''
) k order by d
drop table #t'
--print @sql
go
- 根据表名生成对该表操作的存储过程
- 根据表名生成有关SelectAll的存储过程
- 根据表名生成有关Update的存储过程
- oracle存储过程 根据表名生成hibernate实体
- SQL之根据表名动态查询的存储过程
- oracle根据表名查询相关的存储过程
- DB2 根据表名查找存储过程
- 根据表名,自动生成增、删、改参数化存储过程和调用代码
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- Oracle中存储过程传入表名,动态清除该表的数据
- ORACLE 存储过程:传表名,栏位名,获取表该栏位的最大值
- MSSQL根据表名动态分页的存储过程以及C#.net调用使用
- 根据字段查询包含该字段的表名
- 存储过程实现给所有的数据库运行一条操作语句,自动根据tc_截取,在表前面加上数据库名
- 通过C#程序生成数据库的实体类,根据SqlServer存储过程生成数据操作类
- java 临时笔记
- Win32环境下dll编程原理
- 小故事大哲理之三
- 小故事大哲理之四
- WML字符使用基本规则
- 根据表名生成对该表操作的存储过程
- 小故事大哲理之五
- 如何使VS.Net 2005 开发平台转为 中文
- 小故事大哲理之六
- 学习NHibernate--第一天
- 小故事大哲理之七
- 小故事大哲理之八
- Say No to Perfection
- Javascript客户端验证常用函数