获取SqlServer 2005中字段的备注信息--downmoon

在上篇文章 中，介绍了如何获取SqlServer 2000中字段的备注信息
本文将介绍如何获取SqlServer 2005中字段的备注信息（downmoon)

Declare   @tblName   nvarchar ( 1000 )
                                     set   @tblName = ' 表名 '
                                     declare   @TblID   int
                                     set   @TblID = ( select   [ object_id ]   as  tblID   from  sys.all_objects  where   [ type ]   = ' U '   and   [ name ] <> ' dtproperties '   and   [ name ] = @tblName ) 
                                     select  syscolumns.name  as  ColumnName,
                                    systypes.name  as  ColumnType,
                                    syscolumns.length  as  ColumnLength,
                                    ( SELECT     [ value ]   FROM   ::fn_listextendedproperty( NULL ,  ' user ' ,  ' dbo ' ,  ' table ' ,  object_name ( @TblID ),  ' column ' , syscolumns.name)   as  e  where  e.name = ' MS_Description ' )  as  ColumnDescription
                                                 from  sysColumns 
                                                 left   join  sysTypes  on  sysTypes.xtype  =  sysColumns.xtype  and  sysTypes.xusertype  =  sysColumns.xusertype 
                                                 left   join  sysobjects  on  sysobjects.id  =  syscolumns.cdefault  and  sysobjects.type = ' D '  
                                                 left   join  syscomments  on  syscomments.id  =  sysobjects.id 
                                     where  syscolumns.id = @TblID