正好用到这个东西, 网上找来找去貌似都是同一个版本, 看不明白.
所以自己研究了一下.
大致是用到了SQL2000的3个系统表:SysColumns, SysObjects 和 SysProperties
SQL中字段属性并非直接可以在RecordSet.Fields对象中查找到. 而是保存在SysProperties表的Value字段中(需要转换成字符串类型), 并通过SysProperties.ID字段关联到SysObjects.ID对应用户定义的Table表名,再通过SysProperties.SmallId关联到SysColumns.ColId对应到用户定义的Table中的字段名.
既然三者关系找到了,那么就能通过一个SQL来列出用户表的字段备注信息了.
网上看到的都是很高级的Join写法, 我不懂那个,所以理解不了, 只会最老土的Select From Where写法.
于是就殚精竭虑倒腾出一个来,试了一下,也能用.
语句为:
Select SysObjects.Name As TableName, FieldName, FieldInfo, FieldIndex From SysObjects , (Select SysColumns.Name as FieldName, SysColumns.Id, Convert(Char,[Value]) As FieldInfo, ColId As FieldIndex From SysColumns, SysProperties Where SysColumns.Id = SysProperties.Id
And Colid = Smallid) As LUT Where SysObjects.Id = LUT.Id Order By TableName, FieldIndex
结构化一下:
Select
SysObjects.Name As TableName,
FieldName,
FieldInfo,
FieldIndex
From
SysObjects,
(Select
SysColumns.Name as FieldName,
SysColumns.Id,
Convert(Char,[Value]) As FieldInfo,
ColId As FieldIndex
From
SysColumns,
SysProperties
Where
SysColumns.Id = SysProperties.Id
And
Colid = Smallid) As LUT
Where
SysObjects.Id = LUT.Id
Order By
TableName,
FieldIndex
查询得到的记录集格式为:
TableName, FieldName, FieldInfo, FieldIndex
(表名,字段名,字段备注,字段索引)
就这样了, 为了避免下次忘记, 特立此文.