一个不错的存储过程——获取表字段等信息

 
/****** 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不支持问题

执行结果