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

最新推荐文章于 2023-04-17 12:34:44 发布

faunjoe

最新推荐文章于 2023-04-17 12:34:44 发布

阅读量454

点赞数

分类专栏： SQLServer 文章标签： sqlserver join object table null user

本文链接：https://blog.youkuaiyun.com/faunjoe/article/details/4535362

版权

SQLServer 专栏收录该内容

90 篇文章

订阅专栏

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

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/downmoon/archive/2007/09/28/1805426.aspx