SQL Server 2008 - Get Table Constraints

Question

Could you help me create a query that retrieves the constraints in all tables, the number of constraints in each table, and also display NULL for tables that DO NOT have any constraints. thanks in advance!

This is what I still have:

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 current sys catalog views (if you are on SQL Server 2005 or newer - sysobjects views are outdated ) and should be avoided) - check out the extensive MSDN documentation documentation for SQL Server Books Online for catalog types here .

There are several views that may interest you:

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

and much more - check it out!

You can query and join these views to get the necessary information - for example, it will display tables, columns and all the 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 2

I used the following query to get restrictions information in SQL Server 2012 and it works fine. Hope this will be helpful 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 3

 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]) ) 

Answer 4

I tried to edit the answer provided by marc_s , however for some reason it was not accepted. It formats sql for easy reading, includes a schema and also a default name, so that it can be easily inserted 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 can get this request

Unique limitation

The default constraint is with the value

Foreign key with reference table and column

And the limitation of the primary key.

 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');