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

Why would you store this information yourself, its already stored and it bring a lot of maintenance with it? — bobthedeveloper, Jan 02 '15 at 11:39

score 19 · Answer 1 · edited Jan 02 '15 at 12:03

19

USE INFORMATION_SCHEMA.TABLE_CONSTRAINTS

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

edited Jan 02 '15 at 12:03

Pரதீப்

91,748
19
131
172

answered Jan 02 '15 at 11:42

knkarthick24

3,106
15
21

This should be marked as the correct answer – enanone Dec 08 '20 at 11:14

How to get all constraint on table?

1 Answers1