[转]SQL Server 2008- Get table constraints

本文转自:https://stackoverflow.com/questions/14229277/sql-server-2008-get-table-constraints

You should use the current sys catalog views (if you're on SQL Server 2005 or newer - the sysobjects views are deprecated and should be avoided) - check out the extensive MSDN SQL Server Books Online documentation on catalog views here.

There are quite a few views you might be interested in:

  • sys.default_constraints for default constraints on columns
  • sys.check_constraints for check constraints on columns
  • sys.key_constraints for key constraints (e.g. primary keys)
  • sys.foreign_keys for foreign key relations

and a lot more - check it out!

You can query and join those views to get the info needed - e.g. this will list the tables, columns and all default constraints defined on them:

SELECT 
    TableName = t.Name,
    ColumnName = c.Name,
    dc.Name,
    dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
ORDER BY t.Name

 

posted on 2017-11-30 16:14 freeliver54 阅读(...) 评论(...) 编辑 收藏

导航

公告