T-SQL: Check Database Consistency Using Visual Studio SSDT

  • Article

DOWNLOAD
the
CODE

All Code used in this article can be downloaded from this URL.

 

Introduction

This article is about how to use SSDT in Visual Studio to check database consistency. The inspiration for this article was this question that was asked in TechNet Transact-SQL Forum.

Problem

Changing database objects is a very common task for both Developers and DBAs. These changes can impact database consistency. Before dropping any object, it is important to consider what is dependent on that object. Once it is dropped, these are all going to error.
We can see this problem with a sample. The following script creates a database with three objects.

Code 01

CREATE DATABASE  Test;
GO
 
USE Test;
GO
 
--table 
CREATE TABLE  Books
( BookId INT  PRIMARY KEY,
  Title NVARCHAR(500),
  Qty   INT  );
GO
 
--view
CREATE VIEW  vBooks
AS
SELECT * 
FROM dbo.Books;
GO
 
--procedure
CREATE PROC uspSearchBooks
@Title NVARCHAR(500)
AS
SELECT * 
FROM dbo.vBooks
WHERE Title LIKE  '%' + @Title + '%';
GO

The next script drops the Qty column from the Books table. Our search procedure still expects that Qty column to be there and it will not work. Because this procedure uses a view that used this column. This shows in the following code:

Code 02

--drop column
ALTER TABLE  dbo.Books 
  DROP COLUMN  Qty;
GO
 
--test the procedure
EXEC dbo.uspSearchBooks 
  @Title = N'New Life'

Pic 01

The error itself does not help us to verify which object is missing. Such references can be recovered from the databases metadata views like sys.sql_expression_dependencies and sys.sql_dependencies.  That's fine if we are making this change ourself. If you change something then you know what you did.
The big problem comes when someone else does this. The main problem is that in any situation we do not know the dropped column. Another problem is that these metadata views do not track the dynamic SQL scripts within procedures. So, what is the solution?

Solution

The solution is to use SSDT in Visual Studio. SSDT stands for SQL Server Data Tools which adds a database project template to Visual Studio. It gives the ability for continues integration (CI) in databases, which is not the purpose of this article.

The key concept for solving our problem is to import the database to Visual Studio as a database project using SSDT. Then we can build this solution. If there are missing objects we can see them in the error list pane, otherwise our build will complete successfully.

Now, we can see this in action. The first step is creating a new SQL Server Database Project:

Pic 02

We have to import our Test database to this project by using Solution Explorer:

Pic 03

Pic 04

After importing the database, we can build our solution:

Pic 05

Now, we can see the error list which shows that Qty column is used in vBooks view:

Pic 06

We can use the following code to resolve this error:

Code 03

--view
ALTER VIEW  dbo.vBooks
AS
SELECT BookId, Title
FROM dbo.Books;
GO

We can import this change to our database project by using Schema Compare:

Pic 07

Now, we have to change the direction and select our database as source and click compare button:

Pic 08

We can import database changes to our database project by using the Update button:

Pic 09

After updating our database project, the error list will change and tells us error about the missing object in the search procedure. We can resolve missing references it in our database till the error list will become empty and our build will be successful.

Conclusion

Resolving missing database objects can become a very simple task using the database project in Visual Studio. We saw the steps to achieve this solution. This solution is more reliable than querying SQL Server system views. 

DOWNLOAD
the
CODE

All Codes used in this article is downloadable from this URL.

** **

See Also

Other Resources