--获取表信息 SELECT [Table Name] = OBJECT_NAME(c.object_id), [Column Name] = c.name, [Description] = ex.value FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND OBJECT_NAME(c.object_id) = 'HR_Survey' --写表名 -- 这里加上你的表名字, ORDER BY OBJECT_NAME(c.object_id), c.column_id
--查询表字段和字段类型【这里的2个系统表应该换成sys.types 、 sys_columns】 select c.name as colname, b.name as typename from syscolumns c,systypes b where c.xusertype=b.xusertype and c.id=object_id('HR_Survey')
3楼的执行结果 eg:--Table Name Column Name Description ------------ ------------- ------------ --HR_Survey id 编号 --HR_Survey Title 标题 --HR_Survey AgPersonID 对象 --HR_Survey AgPerson 对象编号 --HR_Survey Re 事项说明 --HR_Survey AddUserId 添加人ID --HR_Survey AddTime 添加时间 --HR_Survey EditUserId 编辑人ID --HR_Survey EditTime 编辑时间
sys.extended_properties ex 联立sys.columns通过ID号
LEFT JOIN SYSTYPES B
FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
SELECT
[Table Name] = OBJECT_NAME(c.object_id),
[Column Name] = c.name,
[Description] = ex.value
FROM sys.columns c
LEFT OUTER JOIN sys.extended_properties ex
ON
ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name = 'MS_Description'
WHERE
OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
AND OBJECT_NAME(c.object_id) = 'HR_Survey' --写表名
-- 这里加上你的表名字,
ORDER
BY OBJECT_NAME(c.object_id), c.column_id
select c.name as colname, b.name as typename
from syscolumns c,systypes b
where c.xusertype=b.xusertype and c.id=object_id('HR_Survey')
eg:--Table Name Column Name Description
------------ ------------- ------------
--HR_Survey id 编号
--HR_Survey Title 标题
--HR_Survey AgPersonID 对象
--HR_Survey AgPerson 对象编号
--HR_Survey Re 事项说明
--HR_Survey AddUserId 添加人ID
--HR_Survey AddTime 添加时间
--HR_Survey EditUserId 编辑人ID
--HR_Survey EditTime 编辑时间
联立sys.columns通过ID号
SYSTYPES B
SYSCOLUMNS A
LEFT JOIN
SYSTYPES B
ON
A.XUSERTYPE=B.XUSERTYPE