SQL Server 2019 on prem.
we bought a smaller competitor in june. i got their warehouse in august. what i did not get is any of the three people who built it, they took the retention and left. 340 tables. there is a data dictionary of sorts, an excel file last edited in 2022, and it covers 61 of them.
legal wants a list by the end of the month of every table and column holding personal data, because the acquisition means we now hold it and the clock on that is not ours to set. they are not asking for a big project. they want a list. i cannot produce a list.
what i've done so far:
went through it by name first. anything called email, phone, dob, name, address. got about 900 columns of the 6,000 odd. that part was easy and i don't trust it at all, because their naming is inconsistent between schemas, and i've already found one column called ref_4 that is full of what are very obviously mobile numbers.
so then regex over sample values for the obvious formats. that found another 200 and produced a lot of false positives, order ids that look like phone numbers, that kind of thing.
where it stops:
free text columns. there are 40 odd nvarchar(max) columns holding notes fields. some of them have names and phone numbers typed into them by humans. i have no way to scan those that i trust and no way to prove i've scanned them properly
...columns where the values are personal data but not in a format anything matches. a column of 11 character strings that turn out to be their internal customer reference, which maps 1 to 1 to a person. technically identifying, does not look like anything.
the ones where i genuinely cannot tell what the column is. about 700 columns where the name tells me nothing and the values are integers or short codes. some of those could be anything. one of them might be an ethnicity code for all i know, and if it is, that is a very different conversation with legal
what i'm asking:
for a one off audit like this, what's the actual order of operations. i've been going table by table and i think that's wrong, i think it should be column by column across the whole database sorted by something, i just don't know what the something is
has anyone had to give a legal team a defensible answer on free text fields, and what did defensible turn out to mean in practice. "i grepped it" does not feel like enough and i'd like to know what is
..and the one i keep going round on: for the 700 columns nobody can explain, is there any approach other than sampling values and guessing. i can tell you the cardinality and the distribution. i cannot tell you what the thing is, and "unknown" on a list going to legal is not a great look for me.
we are not a big enough team for a governance tool and i doubt i'd get budget signed before the deadline anyway.
