Constraints are actually found in two views, INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE. Let’s look at table constraints first.
select constraint_name, table_name, constraint_type
This returns a simple list of the names of the constraints for each table with a constraint in your system. If the table does not have a constraint, it won’t be found here. Finally, the constraint type will either read “PRIMARY KEY” or “FOREIGN KEY”.
To gather more info about constraints, we have to shift to the key_column_usage view. This view shows the fields associated with a constraint.
select constraint_name, table_name, column_name, ordinal_position
By now most of these fields should be familiar. You’ll note though the one drawback, within this view there’s no way to tell whether the constraint is a primary key or foreign key. However, a little SQL magic to combine these two views will solve this dilemma.
select kcu.constraint_name, tc.constraint_type, kcu.table_name,
from information_schema.key_column_usage kcu,
where kcu.constraint_name = tc.constraint_name;
You should know there is another table that lists constraints, the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS. This view only shows referential constraints, not primary keys. In this case we’re interested in documenting all constraints, so there’s nothing here we cannot gather from the other two tables.
And there you go, a simple way to determine the constraints attached to the tables in your SQL Server 2005 Compact Edition tables.