Post reply

Discovering Foreign Key Constraints

  • February 24, 2011 at 11:16 pm

    #234274

    I have a denormalized table that provides code values for various lookups. I am doing a proof of concept to breakout each of the sets of values into their own tables, and I need to delete the data from the old table to prove that it is working right. I've used a query from Dave Pinal's blog to identify the foreign key constraints that need to be dropped before I can delete the data, but it's not working correctly because when I try to truncate the table SQL keeps telling that there are still foreign key constraints on the table. Does anyone have a better method of finding all foreign key constraints on a table?

    Thanks for the help!;-)

    Brandon_Forest@sbcglobal.net

    From Dave Pinal's blog:

    SELECT

    CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,

    TABLE_CATALOG = FK.TABLE_CATALOG,

    TABLE_SCHEMA = FK.TABLE_SCHEMA,

    TABLE_NAME = FK.TABLE_NAME,

    COLUMN_NAME = FK_COLS.COLUMN_NAME,

    REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,

    REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,

    REFERENCED_TABLE_NAME = PK.TABLE_NAME,

    REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME

    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

    ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG

    AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA

    AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

    AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG

    AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA

    AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

    AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

  • February 25, 2011 at 8:06 am

    #1291547

    Double post...please direct all replies here:

    http://www.sqlservercentral.com/Forums/Topic1069402-392-1.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply