--这是属性,tableID 在 sysobjects 表中,可以用 name 检索:select id tableID,smallid columnID ,value property from dbo.sysproperties
從Books on line查來的。:(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default) fn_listextendedproperty Returns extended property values of database objects.Syntax fn_listextendedproperty ( { default | [ @name = ] 'property_name' | NULL } , { default | [ @level0type = ] 'level0_object_type' | NULL } , { default | [ @level0name = ] 'level0_object_name' | NULL } , { default | [ @level1type = ] 'level1_object_type' | NULL } , { default | [ @level1name = ] 'level1_object_name' | NULL } , { default | [ @level2type = ] 'level2_object_type' | NULL } , { default | [ @level2name = ] 'level2_object_name' | NULL } ) Arguments {default|[@name =] 'property_name'|NULL}Is the name of the property. property_name is sysname. Valid inputs are default, NULL, or a property name.{default|[@level0type =] 'level0_object_type'|NULL}Is the user or user-defined type. level0_object_type is varchar(128), with a default of NULL. Valid inputs are USER, TYPE, default, and NULL.{default|[@level0name =] 'level0_object_name'|NULL}Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.{default|[@level1type =] 'level1_object_type'|NULL}Is the type of level 1 object. level1_object_type is varchar(128) with a default of NULL. Valid inputs are TABLE, VIEW, PROCEDURE, FUNCTION, DEFAULT, RULE, default, and NULL. Note Default maps to NULL and 'default' maps to the object type DEFAULT. {default|[@level1name =] 'level1_object_name'|NULL}Is the name of the level 1 object type specified. level1_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.{default|[@level2type =] 'level2_object_type'|NULL}Is the type of level 2 object. level2_object_type is varchar(128) with a default of NULL. Valid inputs are COLUMN, PARAMETER, INDEX, CONSTRAINT, TRIGGER, DEFAULT, default (which maps to NULL), and NULL.{default|[@level2name =] 'level2_object_name'|NULL}Is the name of the level 2 object type specified. level2_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.Tables Returned This is the format of the tables returned by fn_listextendedproperty.
SELECT o.name as tableName,c.name as columnName,p.value as Description FROM sysproperties p join sysobjects o on o.id=p.id join syscolumns c on p.id=c.id and p.smallid=c.colid order by 1
给你放一个自己写的从sqlserver2000中生成数据库字典的办法,我自己用着还是很方便的,2000有了描述,比7.0要强。 select b.name as col ,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type ,b.length ,case b.isnullable when 0 then 'N' else 'Y' end as [isnull] ,isnull(e.text,'') as [default] ,isnull(c.value,'') as descript from sysobjects a,syscolumns b left outer join sysproperties c on b.id = c.id and b.colid = c.smallid left outer join syscomments e on b.cdefault = e.id where a.id = b.id and a.id = object_id('YourTable') order by b.colid其中YourTable是你要查看的表名,语句有点长,2000下运行没有问题,7.0由于没有sysproperties表,所以去掉这个表,和描述字段也可以运行。 本来还想加上主关键字标识,但是没想出在一个sql语句中标识出哪个字段是主关键字的方法,所以只好写了个存储过程,单独列出每个表的主关键字的字段列表。这里就不多写了,我会新开一个帖子来讨论关于表的主关键字字段的查找问题的。
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)
fn_listextendedproperty
Returns extended property values of database objects.Syntax
fn_listextendedproperty (
{ default | [ @name = ] 'property_name' | NULL }
, { default | [ @level0type = ] 'level0_object_type' | NULL }
, { default | [ @level0name = ] 'level0_object_name' | NULL }
, { default | [ @level1type = ] 'level1_object_type' | NULL }
, { default | [ @level1name = ] 'level1_object_name' | NULL }
, { default | [ @level2type = ] 'level2_object_type' | NULL }
, { default | [ @level2name = ] 'level2_object_name' | NULL }
) Arguments
{default|[@name =] 'property_name'|NULL}Is the name of the property. property_name is sysname. Valid inputs are default, NULL, or a property name.{default|[@level0type =] 'level0_object_type'|NULL}Is the user or user-defined type. level0_object_type is varchar(128), with a default of NULL. Valid inputs are USER, TYPE, default, and NULL.{default|[@level0name =] 'level0_object_name'|NULL}Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.{default|[@level1type =] 'level1_object_type'|NULL}Is the type of level 1 object. level1_object_type is varchar(128) with a default of NULL. Valid inputs are TABLE, VIEW, PROCEDURE, FUNCTION, DEFAULT, RULE, default, and NULL. Note Default maps to NULL and 'default' maps to the object type DEFAULT.
{default|[@level1name =] 'level1_object_name'|NULL}Is the name of the level 1 object type specified. level1_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.{default|[@level2type =] 'level2_object_type'|NULL}Is the type of level 2 object. level2_object_type is varchar(128) with a default of NULL. Valid inputs are COLUMN, PARAMETER, INDEX, CONSTRAINT, TRIGGER, DEFAULT, default (which maps to NULL), and NULL.{default|[@level2name =] 'level2_object_name'|NULL}Is the name of the level 2 object type specified. level2_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.Tables Returned
This is the format of the tables returned by fn_listextendedproperty.
FROM sysproperties p
join sysobjects o on o.id=p.id
join syscolumns c on p.id=c.id and p.smallid=c.colid
order by 1
select b.name as col
,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type
,b.length
,case b.isnullable when 0 then 'N' else 'Y' end as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as descript
from sysobjects a,syscolumns b left outer join sysproperties c on b.id = c.id and b.colid = c.smallid left outer join syscomments e on b.cdefault = e.id
where a.id = b.id and a.id = object_id('YourTable')
order by b.colid其中YourTable是你要查看的表名,语句有点长,2000下运行没有问题,7.0由于没有sysproperties表,所以去掉这个表,和描述字段也可以运行。
本来还想加上主关键字标识,但是没想出在一个sql语句中标识出哪个字段是主关键字的方法,所以只好写了个存储过程,单独列出每个表的主关键字的字段列表。这里就不多写了,我会新开一个帖子来讨论关于表的主关键字字段的查找问题的。