系统表

原创于 2007-06-18 14:57:00 发布 · 424 阅读

CC 4.0 BY-SA版权

文章标签：

#constraints #object #go #table #database #system

本文介绍了如何使用SQL查询数据库系统表来获取特定表的所有列名及类型、约束等信息。通过几个具体的例子展示了如何从syscolumns和sys.types表中联接查询得到表Bills的列名和类型，以及如何获取表Bills和Packages的外键、主键和检查约束。

In the database there are system tables we can use. We could get all objects that is created within a databasethe from sysobjects and we can get all the columns from table syscolumns. Here is a example to get a table's column name and type.

SELECT * FROM syscolumns S INNER JOIN sys.types T ON S.XTYPE = T.system_type_id WHERE S.id=object_id('Bills')

we also can get all the constraints.

SELECT * FROM sys.foreign_keys WHERE parent_object_id=object_id('Bills')
GO
SELECT * FROM sys.key_constraints WHERE parent_object_id=object_id('Packages')
GO
select * from sys.check_constraints
GO
select * from sys.key_constraints
GO
select * from sys.foreign_keys
GO
select * from sys.default_constraints