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
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
版权声明:本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网