获取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