/****** Object: StoredProcedure [dbo].[GetFields] Script Date: 09/12/2017 11:04:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFields]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetFields]
GO
CREATE procedure [dbo].[GetFields] @tablename varchar(30),@alias varchar(30) = ''
as
-- declare @tablename varchar(500),@alias varchar(30) select @tablename ='功能模块'
declare @listd varchar(8000),@listd1 varchar(8000),@listd2 varchar(8000),@listd3 varchar(8000),@listd4 varchar(8000),@listd5 varchar(8000),@listn varchar(8000),@listn1 varchar(8000),@listn2 varchar(8000)
declare @lists varchar(8000),@listi varchar(8000),@listu varchar(8000),@listj varchar(8000),@listj1 varchar(8000),@listj2 varchar(8000),@sql varchar(8000) ,@sqlx varchar(8000)
select @listd = 'select ''insert into @tablename (@listd2) values (@listd4) '' from @tablename'
select @listd1= '',@listd2='',@listd3='',@listd3='',@listd4='',@listd5=''
select @listn = '''''''+rtrim(isnull(replace(@fieldname,'''''''',''''''''''''),''''))+'''''''
select @listn1= '''''''+rtrim(replace(@fieldname,'''''''',''''''''''''))+'''''''
select @listn2= '''+isnull(convert(varchar(30),@fieldname),0)+'''
select @listi = 'insert into @tablename (@listd1)'
select @lists = 'select @listd1 from @tablename'
select @listu = 'update @tablename set @listd3 from @tablename a,@alias b where a.list=b.list'
select @sqlx= 'declare @fieldname varchar(30),@fieldname varchar(30)
declare mycur cursor for select @fieldname from @tablename
open mycur
fetch next from mycur into @fieldname
WHILE (@@FETCH_STATUS = 0 )
BEGIN
fetch next from mycur into @fieldname
END
close mycur
deallocate mycur'
if (select count(1) from sysobjects where name ='tmp_getfield')=1
drop table tmp_getfield
CREATE TABLE tmp_getfield (序号 int,笔数 int,字段名称 varchar(800),类型 varchar(800),长度 int,小数 int,可空 varchar(800),主键 char(2))
select @listj1='insert into tmp_getfield (序号,笔数,字段名称,类型)
select @xh,isnull(count(1),0),''@fieldname'',''@type''
from @tablename where @fieldname is not null '
select @listj2='insert into tmp_getfield (序号,笔数,字段名称,类型)
select @xh,isnull(count(1),0),''@fieldname'',''@type''
from @tablename where @fieldname is not null and len(@fieldname)>0 and @fieldname <> ''NULL'''
select @alias= case when @alias='' or @alias is null then '' else @alias+'.' end
declare @id int ,@isnull int,@xh int,@cd1 int, @fieldname varchar(30),@type varchar(30) select @xh =1
select @id = id from sysobjects where name = @tablename
declare mycur cursor for select a.name,a.isnullable,b.name from syscolumns a,systypes b
where a.xtype = b.xtype and a.id = @id and a.iscomputed=0 and a.status & 0x80 =0 and b.name <>'time'
open mycur
fetch next from mycur into @fieldname,@isnull,@type
WHILE (@@FETCH_STATUS = 0 )
BEGIN
select @listd1 = @listd1 + @alias + @fieldname + ','
select @listd3 = @listd3 + @fieldname+' = b.' + @fieldname+ ','
print '00000'
IF @type ='money' or @type='int' or @type ='image' or @type ='decimal' or @type ='float'or @type ='datetime' or @type ='smalldatetime' or @type ='ntext' or @type ='text'
select @sql= replace(replace(replace(replace(@listj1,'@xh',@xh),'@type',@type),'@tablename',@tablename),'@fieldname',@fieldname)
else
select @sql= replace(replace(replace(replace(@listj2,'@xh',@xh),'@type',@type),'@tablename',@tablename),'@fieldname',@fieldname)
exec(@sql)
select @cd1 =笔数 from tmp_getfield where 字段名称 = @fieldname
select @cd1 =isnull(@cd1,0)
select @listd2 = case
when @cd1=0 and @isnull =1 then @listd2
else @listd2+@fieldname+','
end,
@listd4 = case
when @cd1=0 and @isnull =1 then @listd4
when (@type='binary') then @listd4 +'0,'
when (@type = 'money'or @type ='int' or @type='decimal' or @type= 'float' or @type ='ntext' or @type ='text' ) then @listd4 + replace(@listn2,'@fieldname',@fieldname)+','
when @isnull =1 then @listd4 + replace(@listn,'@fieldname',@fieldname)+','
when @isnull =0 then @listd4 + replace(@listn1,'@fieldname',@fieldname)+','
end
--select @listd2=@listd2 ,@listd4=@listd4
select @xh=@xh+1
fetch next from mycur into @fieldname,@isnull,@type
END
close mycur
deallocate mycur
print '11111111111111111'
select @listd1=substring(@listd1,1,len(@listd1)-1),@listd3=substring(@listd3,1,len(@listd3)-1)
select @listd2=substring(@listd2,1,len(@listd2)-1),@listd4=substring(@listd4,1,len(@listd4)-1)
select @sql=replace(replace(replace(@listd,'@tablename',@tablename),'@listd4',@listd4),'@listd2',@listd2)
select '导出' 类型,@sql 脚本 ,'游标'类型2,@sqlx 脚本2
union all select '插入' 类型,replace(replace(@listi,'@tablename',@tablename),'@listd1',@listd1) ,'临时','select * into temp'''+@tablename+''' from '+@tablename 脚本2
union all select '查询' 类型,replace(replace(@lists,'@tablename',@tablename),'@listd1',@listd1) ,'除去','if (select count(1) from sysobjects where name ='''+@tablename+''' )>0 drop table view proc function '+@tablename
union all select '修改' 类型,replace(replace(replace(@listu,'@tablename',@tablename),'@alias',@alias),'@listd3',@listd3) ,'删除','delete from '+@tablename
union all select '字段' 类型,@listd1 ,'帮助','sp_help sp_helptext dbo.sp_decrypt getfields'
update tmp_getfield set 长度=length,小数=xscale,可空=case when isnullable=0 then 'not null' else 'null' end ,主键 ='否'
from tmp_getfield a,syscolumns b where a.字段名称=b.name and b.id=@id
update tmp_getfield set 主键 ='是' where 字段名称 in
(select a.name from syscolumns a,sysindexkeys b,sysindexes c,sysobjects d,sysobjects e
where a.id =b.id and a.id =c.id and a.id =d.id and a.colid=b.colid and b.indid=c.indid and e.parent_obj=a.id
and e.name=c.name and d.id =@id )
select * from tmp_getfield
if (select count(1) from sysobjects where name ='tmp_getfield')=1
drop table tmp_getfield
GO
---修改时间:2017年9月12日11:06:48
---修改高版本SQL不支持问题
执行结果