SQL Server-Beziehungen, die eher in gespeicherten Prozeduren als im Schema vergraben sind

Question

Gegenwärtig haben wir nur sehr wenig referentielle Integrität sowie eine Reihe von Tabellen, die sich selbst verbinden (und vielleicht besser als separate Tabellen oder Ansichten dargestellt werden sollten, die sich verbinden).

Das Wissen darüber, wie sich diese Tabellen zueinander verhalten, ist in der Logik der gespeicherten Prozeduren implizit und nicht explizit im Schema enthalten. Wir erwägen, dies zu ändern.

Der erste Schritt besteht darin, die impliziten Beziehungen tatsächlich zu verstehen und zu dokumentieren.

Meine Frage ist also...

Wie lassen sich diese impliziten Informationen am besten extrahieren, ohne dass man jede gespeicherte Prozedur mit dem Auge überprüfen muss? Ich werde alle Tools in Betracht ziehen, mein eigenes SQL schreiben, um die Systemtabellen abzufragen, oder das SQL-DMO-Modell verwenden - oder eigentlich alles, was dem Computer mehr Arbeit und mir weniger Arbeit macht.

Answer 1

Wenn die Beziehungen nur durch Joins in den SPs identifiziert werden, dann werden Sie nicht viel Glück bei der Automatisierung haben.

Es könnte sich lohnen, die Abfragen mit dem Profiler zu erfassen, um zunächst die häufigsten Verknüpfungen zu finden.

Answer 2

Wenn es um Refactoring geht, bin ich ein Mann der alten Schule:

1. Dokumentieren Sie, was Sie haben, und verwenden Sie visuelle Hilfsmittel.
2. Beschreiben Sie - in schriftlicher Form - das Geschäftsmodell, das diese Datenbank abbildet.
3. Wählen Sie Entitäten aus den Beschreibungssubstantiven und dem vorhandenen Schema aus, die Sie haben.
4. Erstellen Sie ein neues ER-Modell und konsultieren Sie dabei die Unternehmen.
5. Erstellen Sie einen neuen DB auf der Grundlage des ER
6. ETL-Daten in die neue Datenbank übertragen und testen.

Answer 3

Sie können verwenden sys.sql_dependencies um herauszufinden, von welchen Spalten und Tabellen ein SP abhängt (hilft, wenn Sie nicht SELECT * in Ihren SPs). Auf diese Weise können Sie sich zumindest einen Überblick über die Kandidaten verschaffen:

referenced_major_id == the OBJECT_ID of the table
referenced_minor_id == the column id: COLUMNPROPERTY(referenced_major_id,
                                                       COLUMN_NAME,
                                                       'ColumnId')

Möglicherweise müssen Sie sp_refreshsqlmodule um sicherzustellen, dass die Abhängigkeiten auf dem neuesten Stand sind, damit das funktioniert. d.h. wenn Sie eine Ansicht ändern, müssen Sie sp_refreshsqlmodule auf jedes nicht schema-gebundene Modul (offensichtlich erlauben schema-gebundene Module von vornherein keine zugrunde liegenden Änderungen - aber Sie erhalten einen Fehler, wenn Sie sp_refreshsqlmodule auf ein schema-gebundenes Objekt), die von dieser Sicht abhängt. Sie können das automatisieren, indem Sie sp_refreshsqlmodule auf diese Objekte:

SELECT *
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                 + QUOTENAME(ROUTINE_NAME)),
                       N'IsSchemaBound') IS NULL
        OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                    + QUOTENAME(ROUTINE_NAME)),
                          N'IsSchemaBound') = 0