How to get all constraint on table?

Question

I want to store tables metadata into other table.So how to get all constraint and its type like whether it primary , unique or foreign key in MS SQL server.

table_name    |   constraint_name   |  constraint_type
--------------|---------------------|-------------------
user          |   pk_user_id        |   PRIMARY_KEY
user_role     |   fk_role           |   FOREIGN_KEY
user          |   unique_email      |   UNIQUE_KEY

Transmissible

Why would you store this information yourself, its already stored and it bring a lot of maintenance with it? – Mawkish

Answer 1

USE INFORMATION_SCHEMA.TABLE_CONSTRAINTS

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='tablename'

Fiora

This should be marked as the correct answer – Skeens

Careful! This does not show DEFAULT constraints. sp_helpconstraint @objname = 'schema.tableName', @nomsg = NULL; might be more appropriate. – Nastassia