Find invalid objects in your databases

June 6th, 2020

DBA has a number of duties that are primarily targeted at supporting database performance capabilities and data consistency. The administrator can use the CHECKDB command to easily verify data consistency; however, in case they need to find an invalid object in a database schema, some difficulties may occur.

Introduction

By invalid, we usually mean different types of objects such as synonyms, functions, packages, procedures, views, etc. that have a reference to non-existing objects or to objects that were changed in some way (e.g. renamed). For instance, you should be careful with synonyms created on objects because when you delete an object, its synonym will get the invalid status, and if you recreate an object, its synonym has to be recompiled as well. When you attempt to use an invalid object, it will throw an error.

You will often discover invalid objects when you run preparation scripts, perform data export or import, upgrade or apply patches. But a really good way to work with them is to perform regular checks on the presence of mismatched objects before and after you introduce changes or upgrade.

The thing is invalid objects are rather ambiguous. Some of them are pretty harmless, so you can fix them with a simple action or a recompilation, which is normally automatically performed by a database when the object is accessed. For instance, there is not much reason to worry about invalid materialized views since they become valid as soon as you add data to the underlying tables.

Alternatively, other objects indicate serious latent issues and cannot be successfully recompiled. This is especially true when the change on the referenced object results in an error in the calling object, thus the latter cannot be recompiled. In both cases, it is crucial to find out the reason and identify these objects in your database before further issues can occur. Depending on the database you use, you can choose from the scripts provided below to find invalid objects.

Oracle

Since Oracle is an intricate and interrelated database, it is often the case that some objects reliant on one another become ‘invalid’. As a rule, they are recompiled automatically on demand, however, this can be very time-consuming, especially when it comes to complex dependencies. The solution is to find these objects and apply different methods to recompile them. Let us show you how to get a list of invalid objects in Oracle with the following query:

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

Finding invalid objects in Oracle

The information you will receive will help you decide what step you should take next to recompile the objects.

If you experience difficulties trying to search for and fix invalid objects manually, dbForge Studio for Oracle may come to your aid as it offers extensive functionality allowing you to reduce the number of errors and rename the objects without breaking dependencies between them. Find out more on how best to recompile invalid objects with this tool here.

SQL Server

SQL Server doesn’t allow finding mismatched objects directly. In most cases, you need to execute a script to make sure the object is invalid. This is extremely inconvenient, though.
For that reason, let’s create a script that will search invalid objects:

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 invalid objects in SQL Server

The script is useful for the primary analysis. However, there are some gaps in it. The main problem is that the script does not show objects with invalid columns or parameters.

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

We will get an error while executing the storage procedure:

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

Executing the storage procedure will throw an error

Moreover, the script will not work on SQL Server 2005. So, we can’t use the provided script as the primary one.

However, SQL Server offers the sp_refreshsqlmodule system procedure. This procedure updates metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes made to their underlying objects.

Thus, the sp_refreshsqlmodule procedure throws an error if an object contains invalid columns or properties. The procedure can be called inside a cursor for each object. If there are no invalid objects, the procedure is completed without errors.

It is important to remember, however, that script objects may have no dependencies or can contain no invalid objects initially. It is not reasonable to verify such objects. SQL Server will take care of that.

The following script can be used for searching invalid objects:

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

CREATE TABLE #objects (
      obj_id INT PRIMARY KEY
    , 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

The same script for 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

Script execution results are as follows (for a test database):

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 doesn’t check an object’s name while creating a synonym. So, a synonym can be created for a non-existing object.

To find all invalid synonyms, you can use the following script:

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

If there is need to add this check to a current script:

...
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

As you can see, metadata way you can extend the standard functionality of SSMS to do your day-to-day database tasks.

If this task seems tiresome and requiring much effort, you can simplify the process and save a substantial amount of time with SQL Complete, which is a code completion add-in for SSMS and VS. This powerful functionality allows you to easily detect mismatched objects through multiple databases and generate effective scripts to manage them.

Conclusion

When working with a database, it is common practice to have a number of invalid objects that hinder your work and cause errors. The important thing to do is to find and validate them in proper time. In this article, we have taken you through some of the most important things to know about invalid objects in Oracle and SQL databases and provided scripts that will assist you in identifying them. We also want to highlight that if you experience difficulties, there are automated ways to work with, identify, and fix invalid objects provided at Devart and other companies.

6 Responses to “Find invalid objects in your databases”

  1. Tola Says:

    Good script. There is a mistake IF XACT_STATE() 0 should be IF XACT_STATE() 0.
    The script to find all invalid synonyms gave me this error though “Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.”

  2. Sergey Syrovatchenko Says:

    Tola, thanks for the note. Try to add COLLATE DATABASE_DEFAULT

  3. M. Lembke Says:

    Thanks for this great article.

    there is a little mistake in the script.

    Wrong
    IF XACT_STATE() 0
    ROLLBACK TRANSACTION
    Correct
    IF XACT_STATE() = 0
    ROLLBACK TRANSACTION

  4. Sergey Syrovatchenko Says:

    Done. M. Lembke, thanks for your note.

  5. JD Says:

    Great script! Trying to Copy a DB and got error “Invalid Object Name ‘xxxxxx’. I was scratching my head for hours trying to figure out how to find this Invalid Object name. This script did the trick.

  6. Maxim Volkov Says:

    Sometimes the script lists ‘inserted’ or ‘deleted’ aliases as invalid object.

Leave a Comment