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

参考文档：