SQL Server 2008- Get Table Constraints

How do I get constraints on a SQL Server table column

This query should show you all the constraints on a table:

select chk.definition
from sys.check_constraints chk
inner join sys.columns col
    on chk.parent_object_id = col.object_id
inner join sys.tables st
    on chk.parent_object_id = st.object_id
where 
st.name = 'Tablename'
and col.column_id = chk.parent_column_id

can replace the select statement with this:

select substring(chk.Definition,2,3),substring(chk.Definition,9,6),substring(chk.Definition,20,5) 

How to get list of constraints of a table along with their respective column names in SQL Server 2008 R2

Sounds like you need CONSTRAINT_COLUMN_USAGE.

Sql Server - Get database constraints of all tables ( default and check constraints)

This may Help..

USE AdventureWorks2014
GO
;WITH ContraintDetails 
AS(

--DEFAULT_CONSTRAINT
SELECT  
    i.TABLE_CATALOG DatabaseName ,
    schema_name(t.schema_id) SchemaName,
    t.[name] TableName,   
    c.name as ColumnName,
    i.DATA_TYPE ColumnType,
    con.type_desc ConstantType,
    con.[name] ConstantName,
    col.[name] + ' : ' + con.[definition] Details
FROM sys.default_constraints con
INNER JOIN sys.objects t on con.parent_object_id = t.object_id
INNER JOIN sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id
INNER JOIN sys.columns c ON col.object_id = c.object_id AND col.column_id = c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS i ON c.name = i.COLUMN_NAME

UNION ALL

--CHECK_CONSTRAINT
SELECT 
    i.TABLE_CATALOG DatabaseName ,
    schema_name(t.schema_id) SchemaName,
    t.[name] TableName,  
    c.name as ColumnName,
    i.DATA_TYPE ColumnType,
    con.type_desc  ConstraintType,
    con.[name] as constraint_name,
    con.[definition] Details
FROM sys.check_constraints con
INNER JOIN sys.objects t on con.parent_object_id = t.object_id
INNER JOIN sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id
INNER JOIN sys.columns c ON col.object_id = c.object_id AND col.column_id = c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS i ON c.name = i.COLUMN_NAME

)
SELECT * FROM ContraintDetails
WHERE TableName = 'Employee' --AND  ColumnName = 'BirthDate'
ORDER BY TableName,ColumnName

Which table stores the information about constraints in SQL Server 2008?

look first at the information schema views:

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

is probably the one you want but there are a couple of others that might hellp you get information you want.

Custom function with check constraint SQL Server 2008

As stated by Martin Smith using a check constraint with a UDF has some problems and might have a negative impact on performance, but if you want to try it anyway this code should work:

CREATE FUNCTION dbo.CheckVenueCapacity (@venue_id int, @capacity int)
RETURNS int
AS 
BEGIN
  DECLARE @retval int
    SELECT @retval = CASE WHEN venue_max_capacity >= @capacity THEN 0 ELSE 1 END
    FROM venues
    WHERE venue_id = @venue_id 
  RETURN @retval
END;
GO

ALTER TABLE events 
  ADD CONSTRAINT chkVenueCapacity 
  CHECK (dbo.CheckVenueCapacity(event_venue_id, event_expected_attendance) = 0); 

Enable constraints of database in SQL Server 2008

Column GEO_EDGE_UID in table dbo.TGEO_EDGE_V contains data that is violating your FK_MP_GN_LE_V foreign key constraint. There is no way you are going to re-enable that constraint with out first cleaning your data.

---

Related Topics