获取导入模板存储过程

-- =============================================
-- Author:		<Michael Wang>
-- Create date: <2010-07-28>
-- Description:	<获取导入模板>
-- =============================================
ALTER procedure [dbo].[Sp_TableColumn3]
@TableName nvarchar(100)
as 
SELECT    
表名=case   when   a.colorder=1   then   d.name   else   ''   end,
字段序号=a.colorder,    
字段名=a.name,      
标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   'True'   else   'False'   end,      
主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (     SELECT   name   FROM   sysindexes   WHERE   indid   in(       SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid     )))   then   N'True'   else   'False'   end,     
类型=b.name,     
占用字节数=a.length,      
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),     
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),      
允许空=case   when   a.isnullable=1   then   'True'else   'False'   end,      
默认值=isnull(e.text,''),      
字段说明=isnull(g.[value],''),
关联查询=isnull(h.[value],'') 
into #t   
FROM   syscolumns   a   left   join   systypes   b   on   a.xtype=b.xusertype      
         inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'        
        left   join   syscomments   e   on   a.cdefault=e.id      
       left   join   sys.extended_properties   g   on   d.id=g.major_id   and   a.colid=g.minor_id  AND g.name='MS_Description'
       left   join   sys.extended_properties   h   on   d.id=h.major_id   and   a.colid=h.minor_id  AND h.name='关联查询'     
--取消下面的注释则只返回有描述的字段    
where   d.name=@TableName 
order   by   a.id,a.colorder 

--声明游标#t
declare @result1 varchar(max)
declare @result2 varchar(max)
SET @result1=''
SET @result2=''
DECLARE @field char(50)
declare @name char(50)
declare my_cursor1 cursor  
for select cast(字段名 as char(15)) 字段名,cast(字段说明 as char(20)) 字段说明 from #t 
--打开游标
open my_cursor1 
declare @db1 char(4)
--取值
fetch next from my_cursor1 
into @field,@name
--循环
while(@@fetch_status=0) 
begin
set @result1=@result1+','+''''+isnull(rtrim(@field),'')+''''
set @result2=@result2+','+''''+isnull(rtrim(@name),'')+''''
--游标指向下一条记录
fetch next from my_cursor1 into @field,@name
end
--关闭游标
close my_cursor1
--释放
deallocate my_cursor1 
declare @rel varchar(max)
set @rel='select '+right(@result1,len(@result1)-1)+' union all select '+right(@result2,len(@result2)-1)
print @rel
exec (@rel)