One of the most important responsibilities of any database administrator is to maintain the integrity of the database, schema, and data stored in the underlying tables of any database. This article will teach us how to identify invalid or broken objects.

A broken or invalid object in a database means it is a database object referencing another object renamed or deleted. For example, if a stored procedure is referencing a table and the table is dropped/renamed, the stored procedure can be considered invalid/broken. Often, we find invalid objects when we deploy any patch or any script on the database, so it is a good practice to regularly check the list of invalid objects so we can fix them proactively.

In this article, I have explained the use cases with a simple demonstration.

Demo Setup

For demonstration, I created a database, named HospitalManagement, and a sample schema containing five tables, two stored procedures, and one view. The details are following:

The schema diagram looks like the following image:

The script to create tables is following:

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

The script to create the stored procedure is here:

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

The script to create a view is the following:

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

Now, First, drop the medications table by running the below query:

DROP TABLE medications

Once a table is dropped, execute the stored procedure named sp_Get_Patient_Medications.

EXEC sp_Get_Doctor_Patient @PatientID = 1

Query screenshot

The stored procedure returns an error because the SELECT statement within the stored procedure uses using medication table to populate the data.

Let us run the SELECT query on vw_PatientData to populate the data of the patients.

USE HospitalManagement
go
SELECT * FROM vw_PatientData vpd

Query Screenshot

It also returns the error binding errors because the SELECT query used to create a view cannot find the underlying table.

How to Find Invalid Objects

Unlike Oracle, SQL Server does not have any meta-data table which can be used to find invalid/broken database objects. Some SQL Tools can be used to find invalid objects; however, we will use a custom script to help us identify invalid or broken database objects.

To do that, first, we must find the object dependency of the dropped database object. For example, we have created a view using two tables, and we want details of the tables used to create a view. Such object dependencies can be found by querying sys.sql_expression_dependencies and sys.all_objects. The following script helps us to 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 [Invalid OBJECT NAME] , custom_error_message [Error Message], invalid_obj_type [Object Type] FROM #invalid_db_objects

Let us run the above script on the HospitalManagement database to find invalid/broken objects.

Query output

The above screenshot shows that the query has returned a list of invalid objects. We can automate the execution of this script using the SQL Server Agent job. The regular evaluation of script output can help us to maintain database schema by removing/fixing the invalid database objects.

Hope this article helps to identify the invalid database objects.