由于工作需要,写了个代码生成器,用到写SQL系统表信息,现总结一下。
/*
//获取数据库扩展属性
//表属性
--枚举一个数据库中的用户定义的表
select * from sysobjects where xtype='U'
//字段属性
1.根据表名获取表Id
select id from sysobjects where [name] = 'Table'
2.根据表Id查询列的 名称,类型Id(xtype),长度(length),描述Id(colorder)
select * from syscolumns where id = Id -- '1666104976'
3.根据上面查询出来的 类型Id(xtype) 到 systypes 中查找 name ,即类型名称。
select name from systypes where xtype = xtype -- '56'
4.根据上面查询出来的 描述Id(colorder) 到 sysproperties 中查找 value ,即字段描述。
select [value] from sysproperties where [id] = colorder -- '1666104976'
*/
获取 字段 名称,类型,大小,长度,描述 信息。
select a.[name] as FieldName, b.[name] as TblName ,c.[name] FieldType , d.[value] as FieldDic , c.length as FieldLen
from syscolumns a,sysobjects b , systypes c , sysproperties d
where (b.[name] = 'TableName') and (a.[id] = b.[id]) and (a.xtype = c.xtype) and (d.[id] = b.[id]) and (d.smallid = a.colorder)