This works 90% of the time, or to be exact, for 22 of the 24 foreign keys in the database I'm working on. But for 2 FKs, the unique_constraint_name does not match any name in table_constraints.

The names are also a little funny. One of them is "environment designator", with a space. The field the FK points to is named "environment_designator", with an underscore. The other has a unique_constraint_name of "filename is unique", which doesn't correspond to anything I see in the definition of either the "from" or the "to" table.

Is there some place else I should look for a match on unique_constraint_name?

Well, this does indeed appear to work. I was hoping for a solution using information_schema because, (a) i'm already using information_schema elsewhere in this app and I'd like to be consistent; and (b) I've never used the sys catalog tables and I just didn't want to get into it. But something that works is always preferable to something that doesn't work, so I think I'll take your solution. If someone else comes along and knows a way to make it work with information_schema, please post! I doubt I'll switch back if the sys cat solution works, but enquiring minds want to know.
–
JayNov 10 '11 at 22:51

Yeah, I think I'm figuring that out. Well I doubt I'll redo existing working code, I think next time around I'll just go with the SQL Server - specific objects. I had this silly idea that it was better to use information_schema because that's supposed to be an industry standard.
–
JayNov 21 '11 at 19:05