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

在上篇文章中，介绍了如何获取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

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/downmoon/archive/2007/09/28/1805426.aspx