本文转自:https://*.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