Verify SQL Objects

SSDT identifies missing database objects.

Yes! In a database with many objects, identifying views, stored procedures, and functions that point to a non-existent column, table, or another object throws an error – the project will not build nor publish. You are protected from dropping an object that is referenced by another.

We will force a compile error when creating an object; the error is also thrown when a referenced object is removed from the project.

Lets wee how this works.. and we’ll keep it short minimizing distractions:

  1. Create Object (With Error)
  2. Review Error List

Create Object (With Error)

  1. Create a folder in stg named Views
  2. Right click the folder Views
  3. Select Add
  4. Select View…
  1. Name the view stg.AccountTransaction_Limit
  2. Click Add
  3. Notice the schema is included and the name elements are qualified with brackets
  4. Edit the CREATE VIEW script by referencing columns from the [stg].[AccountTransaction]
    • For best practice, use a table alias
    • Oh Look IntelliSense
  1. Now add a non-existent column [t].[IdTransaciton]
  2. Notice the red underlining – an error
CREATE VIEW [stg].[AccountTransaction_Limit]
AS
SELECT
	[t].[AcctName],
	[t].[AcctAmount],
	[t].[IdTransaction]
FROM
	[stg].[AccountTransactions]	[t];

Review Error List

Visual Studio’s Error List shows more information about the errors found in the project and solution.

  1. Navigate the menu and click View > Error List
  2. SSDT tells us the error(s):
  1. This is great, because the Error List enumerates the errors in the project and solution
  2. Test this by saving and closing the edited view