sp_sys_GetTableFieldNames 获取资料表的字段备注信息

  sp_sys_GetTableFieldNames 获取资料表的字段备注信息

SQL Code:

ALTER PROCEDURE [dbo].[sp_sys_GetTableFieldNames]
  @TableName VARCHAR(100)
AS
BEGIN
  
  /******************************************************************
  获取表的字段备注信息 by jonny
  
  select * from sys.extended_properties
  
  sp_sys_GetTableFieldNames 'tb_SO'
  sp_sys_GetTableFieldNames 'tb_SOs'
  *******************************************************************/
  
  SELECT
  TableName=d.name,
  FieldOrder=a.colorder,
  FieldName=a.name,
  FieldCaption=case when isnull(g.[value], '')='' then a.name else CAST(g.[value] AS NVARCHAR(250)) end
  FROM syscolumns a
  inner join sysobjects d on a.id=d.id and d.xtype= 'U ' and d.name <> 'dtproperties '
  --left join sysproperties g on a.id=g.id and a.colid=g.smallid --sql 2008
   left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id --sql2005
   where d.name= @TableName --如果只查询指定表,加上此条件
  END
  
  
  //来源:C/S框架网 | www.csframework.com | QQ:23404761