Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2008- Get table constraints

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]  
like image 436
unos Avatar asked Jan 09 '13 06:01

unos


People also ask

How can I get table constraints in SQL Server?

Use the view table_constraints in the information_schema schema. The column table_name gives you the name of the table in which the constraint is defined, and the column constraint_name contains the name of the constraint.

How do I see all constraints on a table in SQL?

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME from information_schema. KEY_COLUMN_USAGE where TABLE_NAME = 'yourTableName'; To display all constraints on a table, implement the above syntax.

How do I view constraints on a table in SSMS?

Click on the plus symbol beside the table name. Folders for the columns, indexes and constraints etc will appear. Click on the plus beside the Constraints folder and any constraints on the table will be displayed. If you wish to view details of the constraint.

How do I display constraints in SQL Server?

We use INFORMATION_SCHEMA.TABLE_CONSTRAINTS to display the constraints. Here, we display the name (CONSTRAINT_NAME) and the type of the constraint (CONSTRAINT_TYPE) for all existing constraints. SELECT INFORMATION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='TABLE_NAME';

How many unique key constraints can a table have?

So, 999 is the restriction for the number of non-clustered indexes on a table, not the unique constraints limit :) and a table can have 1000 unique key constraints.

How do I get information from a table constraint view?

Returns one row for each table constraint in the current database. This information schema view returns information about the objects to which the current user has permissions. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA. view_name. Constraint qualifier.


1 Answers

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 
like image 188
marc_s Avatar answered Oct 21 '22 18:10

marc_s