Search code examples
sqlsql-serverinformation-schema

SQL default value constraint INFORMATION_SCHEMA

I have a column in my table which have a default value constraint 'DF_DOC_DMA_PLACE_1_dma_id'

I want to drop that column but first I need to drop that constraint. The problem is that this table resides in many databases and in some of them the constraint does not exist. How to first check if the constrain exists, then delete it ?

I'm aware of the views in INFORMATION_SCHEMA but I can't find the view with this constraint? Can u tell me where the default value constraints resides ? Thanks

Solution

  • Please use the following query to find the default constraint for a every table.

    SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc = 'DEFAULT_CONSTRAINT'