SQLTeam.com | Weblogs | Forums

Using Information Schema to query constraint from table

mysql

#1

Hello all,

I am relatively new to SQL and I am trying to find out what is the constraint for this particular column in this table of mine.

So the table_schema = "gx_dev", table_name = ''entry_details'' and I found 2 constraint names = "Primary" and "entry_source_id_foreign" from using the following query:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_SCHEMA = 'gx_dev'
and TABLE_NAME = 'entry_details'

Within the table "entry_details", I want to find out if this column "marg" can only input positive digits. I tried the query below to find the constraint but it led to error 1109: unknown table 'CHECK_CONSTRAINTS' in information_schema.

select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
where CONSTRAINT_NAME = "entry_source_id_foreign"

I read that check_constraints should use sys.sysobjects but I have no idea how to go about that, can anybody help please?

Thanks!!!


#2

It's really best not to use the INFORMATION_SCHEMA views in SQL Server. Try this query instead:

SELECT cc.is_disabled, cc.definition AS constraint_definition
FROM sys.check_constraints cc
WHERE cc.object_id = OBJECT_ID('gx_dev.entry_details')