As a database administrator, one of your key responsibilities is to ensure the integrity of the database, schema, and data stored within it. This includes identifying and addressing any invalid or broken objects in the database.

An invalid or broken object in a database refers to an object that references another object that has been renamed or deleted. For example, if a stored procedure references a table that has been dropped or renamed, the stored procedure becomes invalid or broken.

It is important to regularly check for invalid objects in your database, especially after deploying patches or scripts. By proactively identifying and fixing these objects, you can maintain the stability and reliability of your database.

In this article, we will explore how to identify and handle invalid objects in SQL Server using a simple demonstration.

Demo Setup

For this demonstration, we will create a database called “HospitalManagement” with a sample schema containing five tables, two stored procedures, and one view. The tables include “Patients,” “Doctors,” “Appointments,” “Medication,” and “Medical_Records.” The stored procedures are “sp_Get_Doctor_Patient” and “sp_Get_Patient_Medications,” and the view is “vw_PatientData.”

To create the tables, you can use the following script:

USE hospitalmanagement;
CREATE TABLE patients (
    patient_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    address VARCHAR(100),
    phone_number VARCHAR(15),
    emergency_contact_name VARCHAR(50),
    emergency_contact_phone VARCHAR(15)
);
-- Create other tables...

Similarly, you can create the stored procedures and view using the provided scripts.

Finding Invalid Objects

In SQL Server, unlike Oracle, there is no metadata table specifically designed to find invalid objects. However, we can use a custom script to identify invalid or broken database objects.

To find invalid objects, we need to determine the object dependencies of the dropped database object. This can be achieved by querying the system views “sys.sql_expression_dependencies” and “sys.all_objects.”

Here is an example script that helps identify the list of invalid objects:

SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#invalid_db_objects') IS NOT NULL
    DROP TABLE #invalid_db_objects;

CREATE TABLE #invalid_db_objects (
    invalid_object_id INT PRIMARY KEY,
    invalid_obj_name NVARCHAR(1000),
    custom_error_message NVARCHAR(3000) NOT NULL,
    invalid_obj_type CHAR(2) NOT NULL
);

INSERT INTO #invalid_db_objects (invalid_object_id, invalid_obj_name, custom_error_message, invalid_obj_type)
SELECT
    cte.referencing_id,
    obj_name = QUOTENAME(SCHEMA_NAME(all_object.[schema_id])) + '.' + QUOTENAME(all_object.name),
    'Invalid object name ''' + cte.obj_name + '''',
    all_object.[type]
FROM (
    SELECT
        sed.referencing_id,
        obj_name = COALESCE(sed.referenced_schema_name + '.', '') + sed.referenced_entity_name
    FROM sys.sql_expression_dependencies sed
    WHERE sed.is_ambiguous = 0
        AND sed.referenced_id IS NULL
) cte
JOIN sys.objects all_object ON cte.referencing_id = all_object.[object_id];

SELECT
    invalid_obj_name AS [Invalid Object Name],
    custom_error_message AS [Error Message],
    invalid_obj_type AS [Object Type]
FROM #invalid_db_objects;

By running this script on your database, you can obtain a list of invalid or broken objects.

Automating the Process

To streamline the identification of invalid objects, you can automate the execution of the script using SQL Server Agent jobs. By scheduling regular evaluations of the script output, you can proactively maintain your database schema by removing or fixing any invalid objects.

Regularly checking for and addressing invalid objects is a good practice that helps ensure the stability and reliability of your SQL Server database.

We hope this article has provided you with insights into identifying and handling invalid database objects in SQL Server.