sp_sys_GetTableFieldNames 获取资料表的字段备注信息
作者:C/S框架网|www.cscode.ne  发布日期:2021-06-03 12:58:51
  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



上一篇 下一篇