sp_help中获取表字段属性的SQL

昨天老大说他 有工具对比所有表的结构的差异,但我想可能还是那种能比不能生成矫正语句那种,所以这两天想做个能生成同步表结构SQL的小工具.
功能要求:
1.能对比单个表或正多个表之间的差异
2.能根据差异生成同步结构的SQL
3.能生成回滚的SQL方便测试

这时候想起了sp_help这个东东,看了看代码,分离出一段只显示表字段属性的SQL,我想应该用的上,先记录下来,留待备用 :)

declare   @objname   nvarchar ( 776 )
-- 要分析的表的表名
set   @objname   =   ' 要分析的表的表名 '

declare   @objid   int
declare   @sysobj_type   char ( 2 )
select   @objid   =   object_id ,  @sysobj_type   =  type  from  sys.all_objects  where   object_id   =   object_id ( @objname )

declare      @dbname     sysname
        , @no   varchar ( 35 ),  @yes   varchar ( 35 ),  @none   varchar ( 35 )
select   @no   =   ' no ' ,  @yes   =   ' yes ' ,  @none   =   ' none '

declare   @numtypes   nvarchar ( 80 )
select   @numtypes   =  N ' tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney '

select
             ' Column_name '              =  name,
             ' Type '                      =  type_name(user_type_id),
                ' Computed '                  =   case   when   ColumnProperty ( object_id , name,  ' IsComputed ' )  =   0   then   @no   else   @yes   end ,
             ' Length '                      =   convert ( int , max_length),
             ' Prec '                      =   case   when   charindex (type_name(system_type_id),  @numtypes )  >   0
                                         then   convert ( char ( 5 ), ColumnProperty ( object_id , name,  ' precision ' ))
                                         else   '       '   end ,
             ' Scale '                      =   case   when   charindex (type_name(system_type_id),  @numtypes )  >   0
                                         then   convert ( char ( 5 ),OdbcScale(system_type_id,scale))
                                         else   '       '   end ,
             ' Nullable '                  =   case   when  is_nullable  =   0   then   @no   else   @yes   end ,
             ' TrimTrailingBlanks '      =   case   ColumnProperty ( object_id , name,  ' UsesAnsiTrim ' )
                                         when   1   then   @no
                                         when   0   then   @yes
                                         else   ' (n/a) '   end ,
             ' FixedLenNullInSource '      =   case
                         when  type_name(system_type_id)  not   in  ( ' varbinary ' , ' varchar ' , ' binary ' , ' char ' )
                             then   ' (n/a) '
                         when  is_nullable  =   0   then   @no   else   @yes   end ,
             ' Collation '          =  collation_name
from  sys.all_columns  where   object_id   =   @objid