I am currently working on a project where some (significant) data corruption has occurred. Specifically the database is using GUIDs as primary keys on most tables, however it is not enforcing data integrity between those tables (due to a long-winded "this was bought from another company and integrated with our stuff" discussion). There is an effort underway to introduce referential integrity, however the data has to be cleaned up before that is possible.
Part of this data corruption is that different entity keys have been switched or inserted into incorrect columns. For example a valid "ProductId" value exists in a "CustomerId" field due to the software inserting the wrong key in the wrong column. This was happening for months in the database before the issue was finally caught (during a data aggregation job) and now the cleanup effort is rather large.
What I'm wondering is if there is a way of finding a GUID in a database. Rather, if I have a GUID that I think is a primary key in some table, is there a way in T-SQL of asking for matches to any known uniqueidentifier key column?
I know the GUID I have isn't a CustomerId, but it might be another primary key id from some other table. Up to now the team have been searching the other tables manually for matches to the rogue GUIDs to see if they can find out the "type" of the key. They've even gone as far as to create scripts with lots of "select * from [table] where [tableId] = @rogueGUID" statements that search the known entity tables for a match. However, this approach isn't very efficient.
I'm looking for this script not only for this project but also because I think a "Does this GUID exist as a primary key value anywhere in this database?" type of query would be a useful asset in a development toolbox. Any help is appreciated.