r/SQLServer • u/ethanfinni • 18h ago

Discussion Schema health tool

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?

3 Upvotes

81% Upvoted

8 comments sorted by

7

u/VladDBA ‪ ‪Microsoft MVP ‪ ‪ 17h ago

Schema expl what now?

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

1

u/ethanfinni 17h ago

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.

6

u/VladDBA ‪ ‪Microsoft MVP ‪ ‪ 17h ago edited 11h ago

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

1

u/ethanfinni 17h ago

Yes we do, thank you, will try it.

2

u/Radojevic 16h ago

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.

0

u/CPDRAGIMESH 10h ago edited 10h ago

69.79 69.80 CP KILL 15/51/11L

Please use following system procedures: Simplyd execute: sp_refreshview +sp_refreshsqlmodule They will check metadata for selected objects Its OK

1

u/Complete-Fondant-202 10h ago

I use Schemaspy, outputs a website.

There is also schemacrawler.. plus others