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]
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 columnssys.check_constraints
for check constraints on columnssys.key_constraints
for key constraints (e.g. primary keys)sys.foreign_keys
for foreign key relationsand 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