Step by Step to drop foreign keys, please? :(

• jim.rice

  SSC Rookie

  Points: 42

  More actions

  March 18, 2009 at 10:01 am

  #962053

  apologies... I finally figured it out...

  This query will show you all the names:

  SELECT

  FK_Table = FK.TABLE_NAME,

  FK_Column = CU.COLUMN_NAME,

  PK_Table = PK.TABLE_NAME,

  PK_Column = PT.COLUMN_NAME,

  Constraint_Name = C.CONSTRAINT_NAME

  FROM

  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

  INNER JOIN

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

  ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

  INNER JOIN

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK

  ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

  INNER JOIN

  INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

  ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

  INNER JOIN

  (

  SELECT

  i1.TABLE_NAME, i2.COLUMN_NAME

  FROM

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

  INNER JOIN

  INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2

  ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

  WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'

  ) PT

  ON PT.TABLE_NAME = PK.TABLE_NAME

  -- optional:

  ORDER BY

  1,2,3,4

  then you can easily drop them one-by-one with:

  ALTER TABLE [TableName] DROP CONSTRAINT [Name From Above]

  I kept finding "DROP FOREIGN KEY" but it should be "CONSTRAINT"