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

Here's a script to get foreign keys:

    SELECT TOP(150)
       t.[name] AS [Table],
       cols.[name] AS [Column],
       t2.[name] AS [Referenced Table],
       c2.[name] AS [Referenced Column],
       constr.[name] AS [Constraint]
  FROM sys.tables t
 INNER JOIN sys.foreign_keys constr ON constr.parent_object_id = t.object_id
 INNER JOIN sys.tables t2 ON t2.object_id = constr.referenced_object_id
 INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = constr.object_id
 INNER JOIN sys.columns cols ON cols.object_id = fkc.parent_object_id AND cols.column_id = fkc.parent_column_id
 INNER JOIN sys.columns c2 ON c2.object_id = fkc.referenced_object_id AND c2.column_id = fkc.referenced_column_id
 --WHERE t.[name] IN ('?', '?', ...)
 ORDER BY t.[Name], cols.[name]

Answer 2

You Can Get With This Query

Unique Constraint,

Default Constraint With Value,

Foreign Key With referenced Table And Column

And Primary Key Constraint.

Select C.*, (Select definition From sys.default_constraints Where object_id = C.object_id) As dk_definition,
(Select definition From sys.check_constraints Where object_id = C.object_id) As ck_definition,
(Select name From sys.objects Where object_id = D.referenced_object_id) As fk_table,
(Select name From sys.columns Where column_id = D.parent_column_id And object_id = D.parent_object_id) As fk_col
From sys.objects As C
Left Join (Select * From sys.foreign_key_columns) As D On D.constraint_object_id = C.object_id 
Where C.parent_object_id = (Select object_id From sys.objects Where type = 'U'
And name = 'Table Name Here');

Answer 3

I used the following query to retrieve the information of constraints in the SQL Server 2012, and works perfectly. I hope it would be useful for you.

SELECT 
    tab.name AS [Table]
    ,tab.id AS [Table Id]
    ,constr.name AS [Constraint Name]
    ,constr.xtype AS [Constraint Type]
    ,CASE constr.xtype WHEN 'PK' THEN 'Primary Key' WHEN 'UQ' THEN 'Unique' ELSE '' END AS [Constraint Name]
    ,i.index_id AS [Index ID]
    ,ic.column_id AS [Column ID]
    ,clmns.name AS [Column Name]
    ,clmns.max_length AS [Column Max Length]
    ,clmns.precision AS [Column Precision]
    ,CASE WHEN clmns.is_nullable = 0 THEN 'NO' ELSE 'YES' END AS [Column Nullable]
    ,CASE WHEN clmns.is_identity = 0 THEN 'NO' ELSE 'YES' END AS [Column IS IDENTITY]
FROM SysObjects AS tab
INNER JOIN SysObjects AS constr ON(constr.parent_obj = tab.id AND constr.type = 'K')
INNER JOIN sys.indexes AS i ON( (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tab.id) AND i.name = constr.name )
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
                                    AND (ic.index_id=CAST(i.index_id AS int) 
                                    AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE tab.xtype = 'U'
ORDER BY tab.name

Answer 4

I tried to edit the answer provided by marc_s however it wasn't accepted for some reason. It formats the sql for easier reading, includes the schema and also names the Default name so that this can easily be pasted into other code.

  SELECT SchemaName = s.Name,
         TableName = t.Name,
         ColumnName = c.Name,
         DefaultName = dc.Name,
         DefaultDefinition = dc.Definition
    FROM sys.schemas                s
    JOIN sys.tables                 t   on  t.schema_id          = s.schema_id
    JOIN sys.default_constraints    dc  on  dc.parent_object_id  = t.object_id 
    JOIN sys.columns                c   on  c.object_id          = dc.parent_object_id
                                        and c.column_id          = dc.parent_column_id
ORDER BY s.Name, t.Name, c.name

Answer 5

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

Answer 6

SELECT
    [oj].[name] [TableName],
    [ac].[name] [ColumnName],
    [dc].[name] [DefaultConstraintName],
    [dc].[definition]
FROM
    sys.default_constraints [dc],
    sys.all_objects [oj],
    sys.all_columns [ac]
WHERE
    (
        ([oj].[type] IN ('u')) AND
        ([oj].[object_id] = [dc].[parent_object_id]) AND
        ([oj].[object_id] = [ac].[object_id]) AND
        ([dc].[parent_column_id] = [ac].[column_id])
    )