读取SQLSERVER中字段备注信息代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】读取SQLSERVER中字段备注信息

作者:小小超 / 发布于2012/6/13/ 708

读取SQLSERVER中字段备注信息

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

试试其它关键字

　字段备注信息　

同语言下

可能有用的

小小超贡献的其它代码(2)