Skip to content

Systems and records

Legacy database data dictionary recovery after the expert has left

By SourceX Editorial · Updated

9 sections

Short answer

To recover a legacy database's data dictionary after the person who understood it has left, first freeze a copy and harvest what the system still says about itself: catalog comments, keys, lookup tables, reports and screens. Then rebuild each field's meaning from evidence, interviewing remaining users against real records, and grade every definition as confirmed, corroborated, inferred or unknown so no one treats a guess as fact.

System export

Legacy database data

This page covers legacy database data dictionary recovery after the expert has left.Illustrative

Key takeaways

  • Meaning rarely disappears completely; it is scattered across the catalog, the application, old documents, the values themselves and the people who used the screens.
  • Secure the software, reports, code tables and export totals before the interviews, because those are what the interviews will be checked against.
  • Interview people with real records in front of them, not with a blank list of column names.
  • Every recovered definition carries a confidence grade and the evidence behind it, plus the date range it holds for.
  • A dictionary is only finished when it reconciles with the data: every column, every observed code and every blank has an entry or an open question.

Where does the meaning of a legacy database still survive?

It survives in five places, and the person who left was only one of them. The other four are the database catalog, the application that sat on top of it, the documents people wrote around it, and the stored values, which carry patterns that betray what a field was used for.

Start with the catalog because it is the cheapest evidence and it was written at the time. PostgreSQL lets developers attach a comment to a table or a single column with COMMENT ON, and those comments can be read back through the built-in functions obj_description and col_description. SQL Server keeps similar notes as extended properties: the sys.extended_properties view returns one row per property, and Microsoft's own examples name the description property MS_Description, with each value capped at 7,500 bytes.

Most legacy systems have few such comments, but the ones that exist are gold, and a column with a comment that contradicts how the data looks is itself a finding. After the catalog, look at the application layer: entry screens show the human label next to each field, saved reports show which codes were grouped together, and import scripts show which spreadsheet column fed which table.

The last two places are softer. Vendor manuals, support tickets, onboarding notes and month-end checklists often explain a code in passing. People still in the company, such as the order desk lead, the controller or the warehouse supervisor, may never have seen the schema but know exactly what the Hold box on screen three meant.

What has to be captured before the first interview?

Capture everything an interview answer will later be checked against, and do it while the system still runs. Once the server is retired, the vendor contract lapses or the last licensed workstation is wiped, screens and reports are gone, and every definition becomes harder to confirm.

Choose an archive format that will still open without the original software. The Library of Congress describes SIARD, a vendor-neutral package for relational databases, as a ZIP file holding a metadata.xml description of the structure plus one XML file per table. Read the limits before relying on it: the same description notes that SIARD 1.0 does not support time-zone-bearing timestamp types or intervals, so check how those columns would be carried.

What has to be captured before the first interview?
Item to secureWhy it disappearsHow to capture itProof it was captured
Database files and a full backupHardware failure or server retirementCopy to protected storage and restore the copy onceRestore log and file hashes
The software that opens it, with version and licenseLicenses lapse; installers vanishImage the last working client or document the installScreenshot of the version screen
Entry screens with field labelsScreens only exist while the application runsScreenshot every tab of every form usedNumbered screenshot index
Saved reports and queriesStored in the application, not the tablesExport definitions and one sample run of eachReport list with run dates
Lookup and code tablesOften edited or deleted during migrationExport in full with any inactive rowsRow counts per code table
Export totals by table and by yearNeeded later to prove nothing was lostRun counts before any migration stepSigned count sheet
Attachment folders and linked filesLive on a file share outside the databaseCopy with folder paths unchangedFile count and total size
Manuals, tickets and handover notesSit in mailboxes of people who leaveCollect into one folder with sources notedDocument inventory

How do you rebuild structure when keys were never declared?

Rebuild it from naming patterns and value overlap, then label every inferred link as inferred. Many older databases enforced relationships in application code rather than with declared foreign keys, so the catalog shows isolated tables even though the application joined them every day.

Schema documentation tools help with the first pass. SchemaSpy, an open source tool, draws diagrams from declared foreign keys and also from implied relationships, where a column's name and type match another table's primary key. Its schemaMeta file lets a team add comments and relationships the database never declared, which makes it a practical place to record what the interviews confirm.

Then profile the values. For every column, count blanks, distinct values and the earliest and latest dates, and tabulate each code by year. A code that appears only from one year onward, or a flag whose share jumps when a new manager arrived, is a strong hint that the meaning changed, and that is exactly the kind of question to bring to an interview.

Treat a name match as a lead, not a fact. CUST_ID in two tables may point at different customer lists if one table was loaded from an acquired company. Test each implied join by counting orphans: rows whose key finds no match on the other side.

The schema meaning recovery interview, stage by stage

Run the interview in front of real records, with the screens and reports captured earlier, and ask the person to explain specific rows rather than abstract columns. People remember what they did with a record far better than what a field was called, and a walkthrough produces evidence you can cite.

Interview whoever is left who touched the data: long-serving users, the finance team that reconciled it, the vendor's support staff, and the former owner if they can be reached and agree to help. Record each answer with the person's role, the date and the record IDs discussed.

The schema meaning recovery interview, stage by stage
StageQuestion to askEvidence in front of themWhat to record
1. Role and periodWhen did you use this system and for what task?Their user ID activity by year, if availableRole, years of use, screens used
2. Screen walkWalk me through entering one real order from start to finish.Screenshots of each entry tabScreen label to column mapping
3. Record readbackWhat does this row tell you happened?Five to ten real rows, chosen across yearsPlain-language reading of each row, with row IDs
4. Codes and flagsWhen would you set this code, and who else could?Code frequency by year from profilingMeaning, who set it, when usage changed
5. Blanks and defaultsWhat does an empty value here mean?Rows with blanks in the fieldBlank meaning: not applicable, not known, not entered
6. WorkaroundsWhich fields did people reuse for something else?Free-text fields and odd value spikesRepurposed fields and the period of reuse
7. Contradiction checkThis comment says X but the data shows Y; which is right?Catalog comments and conflicting rowsResolution or an open question
8. Who else knowsWho would know the parts you do not?List of remaining open questionsNames or roles to interview next

What does each recovered definition need to record?

Each definition needs its meaning, the period it holds for, the evidence behind it and a confidence grade. Without the grade, a careful definition and a hopeful guess look identical in a spreadsheet, and whoever uses the data next cannot tell them apart.

At a glance

What does each recovered definition need to record

Use four grades. Confirmed means at least two independent kinds of evidence agree, one of them a system artifact or a record walkthrough. Corroborated means one person's account matches the value pattern. Inferred means only the name, type or profile suggests it. Unknown means no evidence yet, and the field stays in the dictionary with an open question rather than being dropped.

For a machine-readable copy, the Frictionless Table Schema specification is a convenient target: each field can carry a description and a list of categories for coded values, and the schema can declare missingValues, primaryKey and foreignKeys. Keep the grade and evidence columns in a companion file so the schema stays valid.

What does each recovered definition need to record?
FieldPurposeAllowed values or formRequired
table_name, column_nameIdentify the column exactly as storedCatalog spelling, case preservedAlways
screen_labelLink to what users sawLabel text and screenshot IDWhen a screen exists
business_meaningWhat the value records, in plain wordsOne or two sentencesAlways, or the text unknown
valid_from, valid_toPeriod the meaning applies toDates or open-endedWhen usage changed
code_listEach code with its meaningCode, meaning, first and last year seenFor coded fields
blank_meaningWhat an empty value meansNot applicable, not known, not entered, mixedAlways
evidence_idsPointers to proofScreenshot, report, comment, interview or row IDsAlways
confidenceHow far the meaning can be trustedConfirmed, corroborated, inferred, unknownAlways
source_rolesWho supplied the meaningRoles and interview dates, no personal opinionsWhen interviews were used
open_questionWhat is still unresolvedQuestion and who might answer itWhen confidence is below confirmed

Worked entries: one that passes and one that fails

The two entries below are illustrative, written for an invented order database; they are not taken from any real company. They show how the same template exposes a weak definition before it reaches anyone downstream.

The passing entry holds up because each claim has a different kind of proof. The failing entry is graded confirmed on the strength of one recollection and covers a field whose values changed partway through the history, so the validator rejects it and the grade drops to corroborated with an open question.

Worked entries: one that passes and one that fails
ElementPassing entry: ORD_HDR.STAT_CDFailing entry: ORD_HDR.CUST_FLG2Rule applied
MeaningOrder status: A open, H on hold, X canceled, C shipped and invoicedProbably credit holdMeaning stated without hedging words
PeriodCodes A, H, X from the first year; C added in a later year after an invoicing changeNone given, although the flag is blank in all early yearsPeriod required when the profile shows a change
EvidenceScreen label Status, status report grouping, and an order desk walkthrough of eight rowsOne former clerk's memoryConfirmed needs two kinds of evidence
Blank meaningNever blank in the dataNot recordedBlank meaning always required
GradeConfirmedLabeled confirmed; validator downgrades to corroboratedGrade must match the evidence rule
Open questionNoneAsk the controller whether the flag drove credit holds or collectionsRequired below confirmed

Which checks prove the dictionary matches the data?

Run checks that compare the dictionary with the catalog and the values, not just with itself. A dictionary that reads well but leaves out a third of the codes in use is a liability, because users will assume the gaps mean nothing.

At a glance

Which checks prove the dictionary matches the data

Record the dictionary version and date with each run, and keep the output. When a migration, archive or buyer review happens later, that run log is the evidence that the definitions were tested against the same data.

  • Coverage: every column in the catalog has a row, including columns that are always blank.
  • Code completeness: every distinct value found in a coded field appears in its code list or is flagged as unexplained, with a count.
  • Evidence rule: every confirmed entry cites at least two kinds of evidence, one of them a system artifact or a record walkthrough.
  • Period coverage: the valid_from and valid_to ranges for a field cover the earliest and latest dates actually present.
  • Join test: each declared or inferred relationship reports its orphan count, and inferred joins stay labeled inferred.
  • Reconciliation: row counts per table and year match the export totals captured before migration.
  • Open questions: every entry below confirmed has a question and a named role who might answer it.

What an interview with a former owner does and does not settle

An interview settles what a field meant; it does not settle who may use the records or the notes. Keep those questions apart from the start, and agree in writing how interview notes and recordings will be used before anyone sits down.

Oral historians draw the same line. The Oral History Association's glossary describes informed consent as documenting that the narrator had the information needed to decide whether to take part, and states that consent does not deal with copyright, which is handled through separate agreements. A business interview is not an oral history, but the distinction is useful: agreement to talk is not permission to publish or reuse.

Keep the rights questions on their own track. Having admin access to the old database does not show the company owns every record in it, and a recovered meaning does not create a right to license. Free-text fields that the interviews revealed were used for personal notes about customers or staff need a privacy review before any use, and the vendor agreement for the old software may restrict how its files are extracted. This is general information; counsel reviews the specific facts.

Bringing a recovered dictionary to a licensing conversation

A graded dictionary makes an old database far easier to assess, whether the next step is a migration, a long-term archive or licensing selected records for AI. Buyers and reviewers ask what each field means and how sure the company is; a dictionary with evidence and open questions answers both.

When licensing is the goal, SourceX takes a company's selected records from rights review through preparation, contracting and delivery as a dataset licensed for AI. Whether the old system's history qualifies is checked through a brief assessment that asks for none of its data, and no record leaves until the company has signed off on scope and terms in an agreement. Fields that stay unknown can simply be left out of the offered scope.

Frequently asked questions

What is a data dictionary for a legacy database?

It is a record of every table and column in the old system with its plain-language meaning, allowed values, what blanks mean and how the meaning changed over time. For a recovered dictionary, each entry also lists the evidence behind it and a confidence grade.

Can you rebuild a data dictionary without the original developer?

Usually yes, for most fields. Catalog comments, entry screens, saved reports, code tables and value profiling explain a large share of a typical schema, and long-serving users can explain the rest against real records. Fields nobody can explain stay in the dictionary marked unknown.

Which tools help reverse engineer an undocumented database?

Schema documentation tools such as SchemaSpy can diagram declared and implied relationships, and simple SQL profiling queries give blank rates, distinct values and date ranges. Tools produce leads; interviews and record walkthroughs turn them into confirmed meanings.

Should we keep the old software after migrating the data?

Keep a working copy, or at least screenshots of every screen and saved report, until the dictionary is finished and checked. Screens and reports often hold the only record of field labels and code groupings, and they disappear with the application.

What if a field's meaning changed over the years?

Record each meaning with its own date range rather than choosing one. Value profiling by year usually shows when the change happened, and an interview can explain why; downstream users then filter or recode by period.

Sources

  • PostgreSQL's COMMENT ON attaches comments to tables and columns, readable through obj_description and col_description. Source
  • SQL Server's sys.extended_properties view returns a row for each extended property, including those on objects and columns. Source
  • Extended property values are sql_variant and limited to 7,500 bytes; Microsoft's examples use the MS_Description property name. Source
  • SIARD packages a relational database as a ZIP with metadata.xml and one XML file per table; version 1.0 does not support time-zone timestamp types or intervals. Source
  • SchemaSpy diagrams declared foreign keys and implied relationships where a column's name and type match a primary key, and accepts added comments and relationships. Source
  • The Table Schema standard lets each field carry a description and categories, and lets a schema declare missingValues and primary and foreign keys. Source
  • The Oral History Association separates informed consent to take part from copyright and use rights, which need separate agreements. Source

See if your company qualifies

A short company assessment. No data uploads are needed.

See if your company qualifies