Very Computer

I need this to work on SQL 7 and 2000.

Thanks!
Jim

use info views...

select *
from information_schema.table_constraints
where table_name='tb'

--
-oj
Rac v2.0 is coming...
http://rac4sql.home.attbi.com

Thanks for the quick response.    The constraint I am looking for is a
column constraint
not a table constraint.

It is not listed in information_schema.table_constraints.

I considered information_schema.check_constraints but
unfortunately the Table Name and Column Name is not also
listed in information_schema.check_constraints.

Do you have any other suggestion?

Thanks!
Jim

table_constraints contains all constraints in the db...

select *
from information_schema.table_constraints
where constraint_type='check'

will give you the same list as check_constraints.

--
-oj
Rac v2.0 is coming...
http://rac4sql.home.attbi.com

Quote:> table_constraints contains all constraints in the db...

All I need now to solve this is to get the constraint, table,
and column in one view...  It's just the column name that's
missing.

Thanks so much,
Jim

The solution is not so pretty but it works for me

CREATE PROCEDURE sp_KillCheckConstraint


AS
BEGIN


(SELECT C.CONSTRAINT_NAME
 FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS C
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
   ON C.CONSTRAINT_NAME = T.CONSTRAINT_NAME

 WHERE T.CONSTRAINT_TYPE='CHECK'



END

-Jim

CREATE PROCEDURE sp_KillCheckConstraint


AS
BEGIN


(SELECT C.CONSTRAINT_NAME
 FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS C
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
   ON C.CONSTRAINT_NAME = T.CONSTRAINT_NAME

 WHERE T.CONSTRAINT_TYPE='CHECK'



END