find number of constraints in a table.

[homeryansta]

hello all! Anyone know how to count all the constraints in a given table? I'm new to stored procedure and need it to finish some work.

[tesuji]

Well, primary key, foreign key, not null, unique, check clause, also sometimes triggers
etc. are or may define constraints. So which of them are meant?

-- tesu

[homeryansta]

Well, primary key, foreign key, not null, unique, check clause, also sometimes triggers
etc. are or may define constraints. So which of them are meant?

-- tesu

Every single one of them.

[tesuji]

I see, all information on them can be found in MySQL's Information-schema tables (system tables, cataloge), for example, information on primary keys, foreign keys etc. is in table_constraints table.

select * from table_constraints;

Enough privileges?

-- tesu

[homeryansta]

I see, all information on them can be found in MySQL's Information-schema tables (system tables, cataloge), for example, information on primary keys, foreign keys etc. is in table_constraints table.

select * from table_constraints;

Enough privileges?

-- tesu

Msg 208, Level 16, State 1, Line 1
Invalid object name 'table_constraints'.

forgot to mention, I'm using MS-SQL 2008

[tesuji]

Sorry, this has been my mistake, I thought we were talking about mysql.
MS sql server has much more comprehensive system tables. You can get a good impression by downloading one of those great posters (such an originally colored poster of sql server 2008 is pinned on a wall in my office)

For example table sys.foreign_key_columns contains all foreign keys of a database..

-- tesu

[homeryansta]

Sorry, this has been my mistake, I thought we were talking about mysql.
MS sql server has much more comprehensive system tables. You can get a good impression by downloading one of those great posters (such an originally colored poster of sql server 2008 is pinned on a wall in my office)

For example table sys.foreign_key_columns contains all foreign keys of a database..

-- tesu

Thank you for your time and posts. I didn't fully understand what I was doing, but my real question is, How do you count the number of rows with constraints in a table. However, I've figured out a way to do that.