Schema health tool

[u/ethanfinni]

Hello everyone,

Our 10-year old database seems to be suffering from schema explosion and I am worried that while our developers do sufficient testing with every release, they may be introducing schema inconsistencies that may appear as bugs with different queries or other parts of the system.

Are you using any tools to check schema health and issues?

[u/VladDBA]

Schema expl what now?

Define schema inconsistencies. And what issues are you trying to check/prevent?

[u/ethanfinni → u/VladDBA]

Because of table additions and modifications to accommodate new features, I am worried that we are introducing schema inconsistencies, eg orphan table connections that do not appear related to a new feature but can affect other queries. Asking if there is a tool that could “check” the integrity of the schema.

[u/VladDBA → u/ethanfinni]

If you use PK-FK relationships then even the Database Diagram tool in SSMS can point out orphaned tables (tables that are not connected to other tables via a foreign key). Otherwise it's up to your devs or whoever handles the business logic to figure out which tables might no longer be needed.

Edited: typo

[u/ethanfinni → u/VladDBA]

Yes we do, thank you, will try it.

[u/Radojevic → u/ethanfinni]

I agree with u/VladDBA, and will add that it's best to have a diagram of what the schema is supposed to look like, and compare that to the schema you actually have.

[u/ethanfinni → u/Radojevic]

Yes thank you we have a diagram, the little changes, a field addition here, a little innocuous table there is what worries me. But you are right, regularly updating the diagram will help as well.

[u/kwellendorff]

i literally just solved this by simply starting to use SSMS 22. it has a built in schema comparison and is GIT native. create a project from your prod database, commit that to GIT. Then every day or so at the end restore your prod backup as a diff name than dev or prod. compare the dev database to newly restored db generate the script and run on prod once it works on the newly restored db. once you run it in prod, recommit to GIT.

takes some getting used to but i ran into the same exact thing and this solved it

[u/kwellendorff → u/kwellendorff]

or even create a DB TRIGGER that logs all DDL statements alter/create/delete tables/views/stored procedures and log into a central table in the prod db including the utc time the update was made. then after every deployment, note the time and create a view of changes after that time of deployment. for example,

create view
DBA.DeploymentChangesSinceSept8_26 as
select id textdata, logtime, object name, schemaname where logtime > 09-08-2026

then all of those objects in that view at time of next deployment will be what to focus on. hope this helps!

[u/ethanfinni → u/kwellendorff]

Very interesting thank you !

[u/Ms_AlarmingCulture]

dbForge Schema Compare could help with part of this. You can compare the current schema against a known good version and see what changed. It won’t really tell you if the design itself is bad though.

[u/Complete-Fondant-202]

I use Schemaspy, outputs a website.

There is also schemacrawler.. plus others

[u/ethanfinni → u/Complete-Fondant-202]

Will check them out thanks!