Searching for Invalid Objects with SQL Complete

The present article deals with one of the bottlenecks in the work of database developers and administrators, namely the occurrence of invalid objects and the ways of finding them in the SQL Server database.

In the previous articles we covered all the way from the idea to the implementation of a database for a recruitment service:

• examined the database basic design rules
• designed a database schema for a recruitment service 
• figured out how to fill a database with test data to provide a growth and performance forecast 
• examined data export and import
• looked at the navigation through the created database, which involves searching for objects and data in a database

How to find invalid objects using SQL Complete

Let’s review the search for invalid objects with the help of the SQL Complete tool. It’s worth saying that dbForge Studio has very similar features. But in this article, we are going to take a look at the search for invalid objects by means of SQL Complete.

Suppose we defined a new table with the employees’ addresses and named it Address:

USE [JobEmpl]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT NULL,
	[Address] [nvarchar](1024) NOT NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
	[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And then defined a linking table between the tables Employee and Address and called it AddressEmployee:
USE [JobEmpl]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AddressEmpoyee](
	[AddressID] [int] NOT NULL,
	[EmpoyeeID] [int] NOT NULL,
 CONSTRAINT [PK_AddressEmpoyee] PRIMARY KEY CLUSTERED 
(
	[AddressID] ASC,
	[EmpoyeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AddressEmpoyee]  WITH CHECK ADD  CONSTRAINT [FK_AddressEmpoyee_Address] FOREIGN KEY([AddressID])
REFERENCES [dbo].[Address] ([AddressID])
GO

ALTER TABLE [dbo].[AddressEmpoyee] CHECK CONSTRAINT [FK_AddressEmpoyee_Address]
GO

ALTER TABLE [dbo].[AddressEmpoyee]  WITH CHECK ADD  CONSTRAINT [FK_AddressEmpoyee_Employee] FOREIGN KEY([EmpoyeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[AddressEmpoyee] CHECK CONSTRAINT [FK_AddressEmpoyee_Employee]
GO

The employees may have different addresses. At the same time, a few employees can have the same address. Hence, the relationship between these entities is many-to-many:

Img.1. The relationship between Employee and Address

We do not consider the case of residence registration when each employee has only one specified address. We are interested in all addresses, including those that specify where an employee tends to spend his time (where he lives and sleeps). There may be a few such places.

And now, let’s create a vEmployeeAddress view that will show the employee’s data and his addresses the following way:

CREATE VIEW vAddressEmpoyee AS
SELECT emp.[EmployeeID]
      ,emp.[FirstName]
      ,emp.[LastName]
	  ,adr.[AddressID]
	  ,adr.[Address]
  FROM [JobEmpl].[dbo].[Employee]		 AS emp
  LEFT OUTER JOIN [dbo].[AddressEmpoyee] AS aep ON emp.[EmployeeID]=aep.[EmpoyeeID]
  LEFT OUTER JOIN [dbo].[Address]		 AS adr ON aep.[AddressID] =adr.[AddressID];

So now we can easily extract the employees and their addresses. 

Supposing with time we decided that addresses are excessive, and it is enough to store the residence registration in the very Employee table:

ALTER TABLE [dbo].[Employee]
ADD [Address] NVARCHAR(1024) NULL;

Since this address is unique for each employee.
We drop the two tables AddressEmpoyee and Address:

USE [JobEmpl]
GO

DROP TABLE [dbo].[AddressEmpoyee];
DROP TABLE [dbo].[Address];
GO

The thing is we forgot to change the vAddressEmployee view, which now refers to the non-existing tables.

Before long, either a user or if we are lucky, a tester discovers a problem, where a part of system functionality crashes whenever it calls the vAddressEmployee view.

To avoid this, every time the changes are introduced into the database, we need to check it for the existence of invalid objects.

For this purpose, select the database you need and in the menu bar of SSMS, select the SQL Complete\Find Invalid Objects command:

Img.2. Selecting the “Find Invalid Objects” command in SQL Complete

In the window that appears, click on the “Analyze” button in the upper left corner or the middle of the window:

Img.3. Running the search for invalid objects

Note that you can select multiple databases at once on the “Databases” panel:

Img.4. Selecting multiple databases

After the search for invalid objects is complete, we can see the result that displays our vAddressEmployee view, which refers to the non-existing tables AddressEmployee and Address:

Img.5. The result of the search for invalid objects

It will be enough to rewrite the vAddressEmpoyee view, taking into account that the address is in the very Employee table, as follows:

ALTER VIEW [dbo].[vAddressEmpoyee] AS
SELECT emp.[EmployeeID]
      ,emp.[FirstName]
      ,emp.[LastName]
	  ,emp.[EmployeeID] AS [AddressID]
	  ,emp.[Address]
  FROM [JobEmpl].[dbo].[Employee]		 AS emp
GO

Once done, when you run the search for invalid objects for the second time, they are not found:

Img.6. None of the invalid objects were found

Note that the AddressID column should not have been shown in the vAddressEmployee view at all. However, if the system uses it, we need to determine the course of changes in two ways:

1. Whether it is possible to substitute AddressID with the value from EmployeeID if the field is used just for information and not for the search for identical addresses.
2. Whether it is possible not to show AddressID at all.

If performing point 1 fails, we will have to introduce changes in the very logic of the application and perform the second point at the same time.

Nevertheless, in case the first point is doable, this will be a quick solution to the problem, and you can later perform the second point with a hotfix or a next update.

In a nutshell, we have considered the importance of finding invalid objects and fixing them.

Conclusion

To sum up, we have looked into the entire process of creating a database for a recruitment service, starting from the idea and finishing with its implementation into production with further changes brought to the schema.

The given database allows us to perform a quick search and to aggregate data according to the following metrics:

1. Employing companies.
2. Positions.
3. Projects.
4. Skills.

It stands to mention that this schema provided the foundation for the IWU TEAM startup.