Menu
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] 
436
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.
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.
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.
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';
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.
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.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
