Re-Trust Untrusted Foreign Keys and Constraints

  • Comments posted to this topic are about the item Re-Trust Untrusted Foreign Keys and Constraints

  • Thanks for the nice script. I needed to change collation on a database which involved ripping out every schema bound object and this came in quite handy with retrusting everything. Thankfully I was able to retrust foreign keys and check constraints with 0 errors. My only suggestion I can think of is to make an easier toggle between fk or ck. It could be as simple as changing your select line to include a commented out line like this:

    SELECT '[' + s.name + '].' + '[' + o.name + ']' AS TableName

    ,i.name AS FKName

    FROM sys.foreign_keys i

    -- FROM sys.check_constraints i

    You could go fancier such as setting a flag that allows either option or both but simply having a commented out line for future reference is nice.

  • Thanks for the script.

  • I am using SQL server 2014 and I have a number of tables with foreign keys where they are set as untrusted. NONE of these FK as set as "NOT FOR REPLICATION" and the command seems to run OK.

    I have  tried dropping the FK and re-creating and it creates ok but is always marked as is_not_trusted = 1
    I have even tried creating a new column of the same datatype and creating a new FK (to the same table) and the same thing happens.
    The FK MAster table looks ok.
    this does not happen to ALL FK just a few (13) out many that exist.

    Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

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