Finden Sie ungültige Objekte in Ihren Datenbanken

November 7th, 2022

DBA hat eine Reihe von Aufgaben, die hauptsächlich darauf abzielen, die Datenbank-Performance und die Datenkonsistenz zu unterstützen. Der Administrator kann den CHECKDB-Befehl verwenden, um die Datenkonsistenz einfach zu überprüfen, falls sie jedoch ein ungültiges Objekt in einem Datenbankschema finden müssen, können einige Schwierigkeiten auftreten.

Einleitung

Unter ungültig verstehen wir normalerweise verschiedene Arten von Objekten wie Synonyme, Funktionen, Pakete, Prozeduren, Ansichten usw., die einen Verweis auf nicht vorhandene Objekte oder auf Objekte, die auf irgendeine Weise geändert (z. B. umbenannt) wurden, haben. Seien Sie beispielsweise vorsichtig mit Synonymen, die auf Objekten erstellt wurden, denn wenn Sie ein Objekt löschen, erhält sein Synonym den Status ungültig, und wenn Sie ein Objekt neu erstellen, muss sein Synonym ebenfalls neu kompiliert werden. Wenn Sie versuchen, ein ungültiges Objekt zu verwenden, wird ein Fehler ausgegeben.

Sie werden oft ungültige Objekte entdecken, wenn Sie Vorbereitungsskripts ausführen, Daten exportieren oder importieren, aktualisieren oder Patches anwenden. Aber eine wirklich gute Möglichkeit, mit ihnen zu arbeiten, besteht darin, regelmäßige Überprüfungen auf das Vorhandensein nicht übereinstimmender Objekte durchzuführen, bevor und nachdem Sie Änderungen oder Upgrades vornehmen.

Die Sache ist, dass ungültige Objekte ziemlich mehrdeutig sind. Einige von ihnen sind ziemlich harmlos, sodass Sie sie mit einer einfachen Aktion oder einer Neukompilierung beheben können, die normalerweise automatisch von einer Datenbank durchgeführt wird, wenn auf das Objekt zugegriffen wird. Beispielsweise gibt es keinen Grund, sich über ungültige materialisierte Ansichten Gedanken zu machen, da sie gültig werden, sobald Sie Daten zu den zugrunde liegenden Tabellen hinzufügen.

Alternativ weisen andere Objekte auf schwerwiegende latente Probleme hin und können nicht erfolgreich neu kompiliert werden. Dies gilt besonders dann, wenn die Änderung am referenzierten Objekt zu einem Fehler im aufrufenden Objekt führt und dieses somit nicht neu kompiliert werden kann. In beiden Fällen ist es wichtig, den Grund herauszufinden und diese Objekte in Ihrer Datenbank zu identifizieren, bevor weitere Probleme auftreten können. Je nach verwendeter Datenbank können Sie aus den unten bereitgestellten Skripts auswählen, um ungültige Objekte zu finden.

Oracle

Da Oracle eine komplexe und miteinander verbundene Datenbank ist, ist es oft der Fall, dass einige Objekte, die aufeinander angewiesen sind, ‘ungültig’ werden. Im Allgemeinen werden sie bei Bedarf automatisch neu kompiliert, besonders wenn es um komplexe Abhängigkeiten geht. Die Lösung besteht darin, diese Objekte zu finden und verschiedene Methoden anzuwenden, um sie neu zu kompilieren. Lassen Sie uns Ihnen zeigen, wie Sie mit der folgenden Abfrage eine Liste ungültiger Objekte in Oracle erhalten:

SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID'

Finding invalid objects in Oracle

Die Informationen, die Sie erhalten, helfen Ihnen bei der Entscheidung, welchen Schritt Sie als Nächstes unternehmen sollten, um die Objekte neu zu kompilieren.

Download a 30-day free trial of dbForge Studio for Oracle

Wenn Sie Schwierigkeiten haben mit der manuellen Suche und Behebung von ungültigen Objekten, kann Ihnen dbForge Studio for Oracle helfen, es umfangreiche Funktionen bietet, mit denen Sie die Anzahl der Fehler reduzieren und die Objekte umbenennen können, ohne die Abhängigkeiten zwischen ihnen aufzuheben. Erfahren Sie hier mehr darüber, wie Sie ungültige Objekte mit diesem Tool am besten neu kompilieren.

SQL Server

SQL Server erlaubt es nicht, nicht übereinstimmende Objekte direkt zu finden. In den meisten Fällen müssen Sie ein Skript ausführen, um sicherzustellen, dass das Objekt ungültig ist. Dies ist jedoch äußerst unpraktisch.

Aus diesem Grund lassen Sie uns ein Skript erstellen, das nach ungültigen Objekten sucht:

SELECT
      obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
    , obj_type = o.type_desc
    , d.referenced_database_name
    , d.referenced_schema_name
    , d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.[object_id]
WHERE d.is_ambiguous = 0
    AND d.referenced_id IS NULL
    AND d.referenced_server_name IS NULL -- ignore objects from Linked server
    AND CASE d.referenced_class -- if does not exist
        WHEN 1 -- object
            THEN OBJECT_ID(
                ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' + 
                ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' + 
                QUOTENAME(d.referenced_entity_name))
        WHEN 6 – or user datatype
            THEN TYPE_ID(
                ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name) 
        WHEN 10 -- or XML schema
            THEN (
                SELECT 1 FROM sys.xml_schema_collections x 
                WHERE x.name = d.referenced_entity_name
                    AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
                )
        END IS NULL

Searching for invalid objects in SQL Server

Das Skript ist für die primäre Analyse nützlich. Allerdings gibt es darin einige Lücken. Das Hauptproblem besteht darin, dass das Skript keine Objekte mit ungültigen Spalten oder Parametern anzeigt.

CREATE VIEW dbo.vw_View
AS SELECT ID = 1
GO

CREATE PROCEDURE dbo.usp_Procedure
AS BEGIN
    SELECT ID FROM dbo.vw_View
END
GO

ALTER VIEW dbo.vw_View
AS SELECT New_ID = 1
GO

The script does not show objects with invalid columns or parameters

Beim Ausführen des Speichervorgangs bekommen wir die Fehlermeldung:

Msg 207, Level 16, State 1, Procedure usp_Procedure, Line 6
Invalid column name 'ID'.

Executing the storage procedure will throw an error

Außerdem funktioniert das Skript nicht auf SQL Server 2005. Daher können wir das bereitgestellte Skript nicht als primäres verwenden.

Download a 30-day free trial of dbForge Studio for SQL Server

SQL Server bietet jedoch die Systemprozedur sp_refreshsqlmodule an. Diese Prozedur aktualisiert Metadaten für die angegebene nicht schemagebundene gespeicherte Prozedur, benutzerdefinierte Funktion, Ansicht, DML-Trigger, DDL-Trigger auf Datenbankebene oder DDL-Trigger auf Serverebene in der aktuellen Datenbank. Persistente Metadaten für diese Objekte, wie z. B. Datentypen von Parametern, können aufgrund von Änderungen an den zugrunde liegenden Objekten veraltet sein.

Daher gibt die sp_refreshsqlmodule-Prozedur einen Fehler aus, wenn ein Objekt ungültige Spalten oder Eigenschaften enthält. Die Prozedur kann innerhalb eines Cursors für jedes Objekt aufgerufen werden. Wenn es keine ungültigen Objekte gibt, wird die Prozedur ohne Fehler abgeschlossen.

Wichtig ist es, nicht zu vergessen, dass Skriptobjekte möglicherweise keine Abhängigkeiten haben oder anfänglich keine ungültigen Objekte enthalten können. Es ist nicht sinnvoll, solche Objekte zu überprüfen. SQL Server kümmert sich darum.

Das folgende Skript kann für die Suche nach ungültigen Objekten verwendet werden:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
    DROP TABLE #objects

CREATE TABLE #objects (
      obj_id INT PRIMARY KEYa
    , obj_name NVARCHAR(1000)
    , err_message NVARCHAR(3000) NOT NULL
    , obj_type CHAR(2) NOT NULL
)

INSERT INTO #objects (obj_id, obj_name, err_message, obj_type)
SELECT 
      t.referencing_id
    , obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
    , 'Invalid object name ''' + t.obj_name + ''''
    , o.[type]
FROM (
    SELECT
          d.referencing_id
        , obj_name = MAX(COALESCE(d.referenced_database_name + '.', '') 
                + COALESCE(d.referenced_schema_name + '.', '') 
                + d.referenced_entity_name)
    FROM sys.sql_expression_dependencies d
    WHERE d.is_ambiguous = 0
        AND d.referenced_id IS NULL
        AND d.referenced_server_name IS NULL -- ignore objects from Linked server
        AND CASE d.referenced_class -- if does not exist
            WHEN 1 -- object
                THEN OBJECT_ID(
                    ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' + 
                    ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' + 
                    QUOTENAME(d.referenced_entity_name))
            WHEN 6 -- or user datatype
                THEN TYPE_ID(
                    ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name) 
            WHEN 10 -- or XML schema
                THEN (
                    SELECT 1 FROM sys.xml_schema_collections x 
                    WHERE x.name = d.referenced_entity_name
                        AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
                    )
            END IS NULL
    GROUP BY d.referencing_id
) t
JOIN sys.objects o ON t.referencing_id = o.[object_id]
WHERE LEN(t.obj_name) > 4 -- hide valid aliases

DECLARE
      @obj_id INT
    , @obj_name NVARCHAR(1000)
    , @obj_type CHAR(2)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT
          sm.[object_id]
        , QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
        , o.[type]
    FROM sys.sql_modules sm
    JOIN sys.objects o ON sm.[object_id] = o.[object_id]
    LEFT JOIN (
        SELECT s.referenced_id
        FROM sys.sql_expression_dependencies s
        JOIN sys.objects o ON o.object_id = s.referencing_id
        WHERE s.is_ambiguous = 0
            AND s.referenced_server_name IS NULL
            AND o.[type] IN ('C', 'D', 'U')
        GROUP BY s.referenced_id
    ) sed ON sed.referenced_id = sm.[object_id]
    WHERE sm.is_schema_bound = 0 -- objects without SCHEMABINDING
        AND sm.[object_id] NOT IN (SELECT o2.obj_id FROM #objects o2)
        AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0
        AND (
              o.[type] IN ('IF', 'TF', 'V', 'TR') --OR o.[type] = 'P' /* Microsoft Connect #656863 */
            OR (
                   o.[type] = 'FN'
                AND
                   -- ignore scalar functions, which are used in DEFAULT/CHECK constraints and COMPUTED columns
                   sed.referenced_id IS NULL
            )
       )

OPEN cur

FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type

WHILE @@FETCH_STATUS = 0 BEGIN

    BEGIN TRY

        BEGIN TRANSACTION
            EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        IF XACT_STATE() != 0
            ROLLBACK TRANSACTION

        INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
        SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type

    END CATCH

    FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type

END

CLOSE cur
DEALLOCATE cur

SELECT obj_name, err_message, obj_type
FROM #objects

The script for finding invalid objects in SQL Server

Dasselbe Skript für SQL Server 2005:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
    DROP TABLE #objects

CREATE TABLE #objects (
      obj_name NVARCHAR(1000)
    , err_message NVARCHAR(3000) NOT NULL
    , obj_type CHAR(2) NOT NULL
)

DECLARE
      @obj_name NVARCHAR(1000)
    , @obj_type CHAR(2)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT
          QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
        , o.[type]
    FROM sys.sql_modules sm
    JOIN sys.objects o ON sm.[object_id] = o.[object_id]
    LEFT JOIN (
        SELECT s.referenced_major_id
        FROM sys.sql_dependencies s
        JOIN sys.objects o ON o.object_id = s.[object_id]
        WHERE o.[type] IN ('C', 'D', 'U')
        GROUP BY s.referenced_major_id
    ) sed ON sed.referenced_major_id = sm.[object_id]
    WHERE sm.is_schema_bound = 0
        AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0
        AND (
              o.[type] IN ('IF', 'TF', 'V', 'TR')
            OR (
                   o.[type] = 'FN'
                AND
                   sed.referenced_major_id IS NULL 
            )
       )

OPEN cur

FETCH NEXT FROM cur INTO @obj_name, @obj_type

WHILE @@FETCH_STATUS = 0 BEGIN

    BEGIN TRY

        BEGIN TRANSACTION
            EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        IF XACT_STATE() != 0
            ROLLBACK TRANSACTION

        INSERT INTO #objects (obj_name, err_message, obj_type) 
        SELECT @obj_name, ERROR_MESSAGE(), @obj_type

    END CATCH

    FETCH NEXT FROM cur INTO @obj_name, @obj_type

END

CLOSE cur
DEALLOCATE cur

SELECT obj_name, err_message, obj_type
FROM #objects

Die Ergebnisse der Skriptausführung lauten wie folgt (für eine Testdatenbank):

obj_name                          err_message                                                                      obj_type
--------------------------------- -------------------------------------------------------------------------------  --------
[dbo].[vw_EmployeePersonalInfo]   An insufficient number of arguments were supplied for ‘dbo.GetEmployee’          V
[dbo].[udf_GetPercent]            Invalid column name ‘Code’.                                                      FN
[dbo].[trg_AIU_Sync]              Invalid column name ‘DateOut’.                                                   P
[dbo].[trg_IOU_SalaryEmployee]    Invalid object name ‘dbo.tbl_SalaryEmployee’.                                    TR
[dbo].[trg_IU_ReturnDetail]       The object ‘dbo.ReturnDetail’ does not exist or is invalid for this operation.   TR
[dbo].[ReportProduct]             Invalid object name ‘dbo.ProductDetail’.                                         IF

SQL Server prüft beim Erstellen eines Synonyms nicht den Namen eines Objekts. So kann ein Synonym für ein nicht existierendes Objekt erstellt werden.

Um alle ungültigen Synonyme zu finden, können Sie das folgende Skript verwenden:

SELECT QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name)
FROM sys.synonyms s
WHERE PARSENAME(s.base_object_name, 4) IS NULL -- ignore objects from Linked server
    AND OBJECT_ID(s.base_object_name) IS NULL

Wenn Sie diese Prüfung zu einem aktuellen Skript hinzufügen müssen:

...
SELECT obj_name, err_message, obj_type
FROM #objects

UNION ALL

SELECT 
      QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name) COLLATE DATABASE_DEFAULT
    , 'Invalid object name ''' + s.base_object_name + '''' COLLATE DATABASE_DEFAULT
    , s.[type] COLLATE DATABASE_DEFAULT
FROM sys.synonyms s
WHERE PARSENAME(s.base_object_name, 4) IS NULL
    AND OBJECT_ID(s.base_object_name) IS NULL

Applying UNION ALL to find invalid objects

Wie Sie sehen können, können Sie mit Metadaten die Standardfunktionalität von SSMS erweitern, um Ihre täglichen Datenbankaufgaben zu erledigen.

Wenn diese Aufgabe mühsame erscheint und viel Aufwand erfordert, können Sie den Prozess vereinfachen und viel Zeit mit SQL Complete sparen, einem Add-In zur Codevervollständigung für SSMS und VS. Diese leistungsstarke Funktionalität ermöglicht es Ihnen, nicht übereinstimmende Objekte in mehreren Datenbanken einfach zu erkennen und effektive Skripts zu ihrer Verwaltung zu generieren.

Download SQL Complete for a free 2-week trial

Schlussfolgerung

Bei der Arbeit mit einer Datenbank ist es üblich, eine Reihe ungültiger Objekte zu haben, die Ihre Arbeit behindern und Fehler verursachen. Das Wichtigste ist, sie rechtzeitig zu finden und zu validieren. In diesem Artikel haben wir Sie durch einige der wichtigsten Dinge geführt, die Sie über ungültige Objekte in Oracle- und SQL-Datenbanken wissen sollten, und Skripts bereitgestellt, die Sie bei der Identifizierung dieser Objekte unterstützen. Wir möchten auch hervorheben, dass es bei Schwierigkeiten automatisierte Möglichkeiten gibt, ungültige Objekte zu bearbeiten, zu identifizieren und zu reparieren, die von Devart und anderen Unternehmen bereitgestellt werden.

Comments are closed.