Tag: legac

Grokking a Legacy Database

Just a quick note today.  Thought I’d share one of my favorite queries for getting to know a legacy database.

Now, we all know that legacy databases are always comprehensively documented, and we never forget where we put that documentation, and the author of the documentation can always predict exactly what you need to know about the database…  but for those of us who live in a world where good practices and intentions don’t universally lead to perfect results:

SELECT
[col].[TABLE_NAME],
[col].[COLUMN_NAME],
[col].[DATA_TYPE],
[col].[CHARACTER_MAXIMUM_LENGTH],
[col].[IS_NULLABLE],
[con].[CONSTRAINT_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [col]
LEFT JOIN [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] AS [con]
ON [col].[TABLE_NAME] = [con].[TABLE_NAME]
AND [col].[COLUMN_NAME] = [con].[COLUMN_NAME]
WHERE [col].[TABLE_NAME] LIKE '%%'
view raw ExamineTables.sql hosted with ❤ by GitHub

Database diagrams make for a nice 5,000 ft. view, but when I’m ready to try to understand a single table nothing beats a quick and simple view of the basic column data.

A view of information returned from the above Gist, when targeting the Employee table in Microsoft's Northwind database.

Just looking at the results for Microsoft’s Northwind database’ Employee table, I know; the Primary Key is EmployeeId, the BirthDate column is a datetime and has a check constraint which is worth investigating, the general structure of the data, and I can intuit that the column ReportsTo, with the Foreign Key constraint FK_Employees_Employees, is likely a cyclic key linking one employee to another in a management hierarchy.

Not bad for a single query.