SQL Server 2008- Get table constraints

Question

Could you help me frame a query that retrieves the constraints in all the tables, the count of constraints in each table, and also display NULL for tables that do NOT have any constraints.

This is what I have so far:

Select  SysObjects.[Name] As [Constraint Name] ,
        Tab.[Name] as [Table Name],
        Col.[Name] As [Column Name]
From SysObjects Inner Join 
(Select [Name],[ID] From SysObjects) As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj] 
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] 
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
order by [Tab].[Name] 

Answer 1

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