Finding Invalid Objects in SQL Server

As a database administrator, one of the most important responsibilities is to maintain the integrity of the database, schema, and the data stored within. This article will guide you through the process of identifying invalid or broken objects in SQL Server, which typically occur when a database object references another object that has been renamed or deleted.

Understanding Invalid Objects

An invalid or broken object in a database is a database object that references another object that has been renamed or deleted. For example, if a stored procedure references a table and the table is dropped or renamed, the stored procedure becomes invalid. Invalid objects often surface when deploying patches or scripts to the database, so it's good practice to regularly check for and fix these objects.

Demonstration Setup

For demonstration purposes, a database named

HospitalManagement

is created with a sample schema containing five tables, two stored procedures, and one view.

Database Objects

Tables:
- Patients: Stores data of patients admitted to the hospital.
- Doctors: Stores details of the doctors working in the hospital.
- Appointments: Stores details of patient-doctor appointments.
- Medication: Stores details of medications prescribed by doctors.
- Medical_Records: Stores details of patients, doctors, diagnoses, and treatments.
Stored Procedures:
- sp_Get_Doctor_Patient
  : Provides details of patients along with doctor's name, diagnosis, and treatment.
- sp_Get_Patient_Medications
  : Provides a list of medications prescribed to the patient.
View:
- vw_PatientData
  : Populates a list of patients admitted to the hospital.

Schema Diagram

The schema diagram looks like the following:

[Include schema diagram here]

Script to Create Tables

sql
USE HospitalManagement
GO
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 TABLE doctors (
    doctor_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    specialization VARCHAR(100),
    phone_number VARCHAR(15),
    years_of_experience INT
);

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    appointment_date DATE,
    appointment_time TIME,
    complaint VARCHAR(500),
    FOREIGN KEY (patient_id) REFERENCES patients (patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)
);

CREATE TABLE medications (
    medication_id INT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    medication_name VARCHAR(100),
    dosage VARCHAR(100),
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (patient_id) REFERENCES patients (patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)
);

CREATE TABLE medical_records (
    record_id INT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    diagnosis VARCHAR(500),
    treatment VARCHAR(500),
    date_of_visit DATE,
    FOREIGN KEY (patient_id) REFERENCES patients (patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)
);

Script to Create Stored Procedures

sql
CREATE PROC sp_Get_Doctor_Patient
@PatientID INT
AS
BEGIN
    SELECT P.first_name + ' ' + P.last_name AS 'Patient Name', P.date_of_birth, P.address,
           D.first_name + ' ' + D.last_name AS 'Doctor Name', MR.diagnosis, MR.treatment
    FROM patients P
    INNER JOIN medical_records MR ON MR.patient_id = P.patient_id
    INNER JOIN doctors D ON MR.doctor_id = D.doctor_id
    WHERE P.patient_id = @PatientID
END;

CREATE PROC sp_Get_Patient_Medications
@MedicationName VARCHAR(100)
AS
BEGIN
    SELECT P.first_name + ' ' + P.last_name AS 'Patient Name', P.date_of_birth, P.address,
           M.medication_name AS 'Medicine Name'
    FROM patients P
    RIGHT JOIN medications M WITH (INDEX(IDX_medications_medication_name)) ON M.patient_id = P.patient_id
    WHERE M.medication_name = @MedicationName
END;

Script to Create View

sql
CREATE VIEW vw_PatientData
AS
SELECT P.first_name + ' ' + P.last_name AS 'Patient Name', P.date_of_birth, P.address,
       D.first_name + ' ' + D.last_name AS 'Doctor Name', MR.diagnosis, MR.treatment,
       M.medication_name, M.dosage
FROM patients P
INNER JOIN medical_records MR ON MR.patient_id = P.patient_id
INNER JOIN doctors D ON MR.doctor_id = D.doctor_id
LEFT JOIN medications M ON P.patient_id = M.patient_id;

Finding Invalid Objects

To illustrate finding invalid objects, drop the

medications

table:

sql
DROP TABLE medications;

Next, try to execute the stored procedure

sp_Get_Patient_Medications

sql
EXEC sp_Get_Doctor_Patient @PatientID = 1;

You will receive an error because the stored procedure references the

medications

table, which no longer exists.

Similarly, running the following query on the view

vw_PatientData

will also return an error:

sql
USE HospitalManagement;
GO
SELECT * FROM vw_PatientData;

Custom Script to Find Invalid Objects

SQL Server does not have a built-in meta-data table to find invalid or broken database objects. However, you can use the following custom script to identify invalid objects by querying

sys.sql_expression_dependencies

and

sys.all_objects

sql
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;

Running the script on the

HospitalManagement

database will produce a list of invalid or broken objects. Here’s the output:

[Include screenshot of query output]

Automating the Check

You can automate the execution of this script using a SQL Server Agent job. Regularly evaluating the script’s output helps maintain the database schema by identifying and fixing invalid objects proactively.

This article should help you effectively identify and manage invalid database objects in SQL Server, ensuring the integrity and performance of your database.