Devart Blog

Find invalid objects in your databases

Posted by on January 4th, 2016

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

ORACLE for instance, allows you to get a list of invalid objects:

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

SQL Server doesn’t allow to do that directly. In most cases, you need to execute a script to see that an object is invalid. This is very inconvenient…
So 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 doesn’t 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

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

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

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 contains the sp_refreshsqlmodule system procedure. The procedure updates the 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 to their underlying objects.

Thus, the sp_refreshsqlmodule procedure generates an error if an object contains invalid columns or properties. The procedure can be called inside a cursor for each object. If no invalid objects exist, then the procedure completes with no errors.

It is important to remember, however, that script objects may have no dependencies or can contain no invalid objects initially. There is no expediency to verify such objects. SQL Server takes 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 doesn’t 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 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, the following script can be used:

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

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

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

  3. Sergey Syrovatchenko Says:

    Done. M. Lembke, thanks for your note.

  4. Sergey Syrovatchenko Says:

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

Leave a Reply