DROP COLUMN

If I execute ALTER TABLE DROP COLUMN XX but the column XX has Constraints
there will be an error.
I should first delete the related Constraints.
Is there any way to delete all of the Constraints related to this column?
Should I navigate before for all of the constraints of the table and delete
the related ones? In this case, how could I do this in VB?
Regards.
Jose Nuez
Montevideo
Jose,
The INFORMATION_SCHEMA view contain what you are after. Here is a sample
query that may be helpful to you:
select y.TABLE_NAME AS ConstrainingTable, x.CONSTRAINT_NAME AS
ConstraintToDisable,
z.TABLE_NAME AS ConstrainedTable, zz.COLUMN_NAME as ConstrainedColumn,
z.CONSTRAINT_NAME AS ConstrainedConstraint
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS x
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE y
on x.CONSTRAINT_NAME = y.CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE z
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE zz
on z.CONSTRAINT_NAME = zz.CONSTRAINT_NAME
on x.UNIQUE_CONSTRAINT_NAME = z.CONSTRAINT_NAME
where y.COLUMN_NAME = 'MyKeyColumn' and z.TABLE_NAME = 'MyConstrainedTable'
RLF
"Jose Nunez" <josenunez70@.hotmail.com> wrote in message
news:uBIE0B7LIHA.5208@.TK2MSFTNGP04.phx.gbl...
> If I execute ALTER TABLE DROP COLUMN XX but the column XX has Constraints
> there will be an error.
> I should first delete the related Constraints.
> Is there any way to delete all of the Constraints related to this column?
> Should I navigate before for all of the constraints of the table and
> delete the related ones? In this case, how could I do this in VB?
> Regards.
> Jose Nuez
> Montevideo
>