SQL Server Dependencies are Fixed! (almost)

If you look at any good article on what is new in SQL Server 2008, buried at the bottom under a bunch of nonsense about spatial data types (I still haven't found anything I cannot do just as with standard data types, in the unlikely event I will ever need to) and other bell's and whistles, it is possible they will mention dependency tracking has changed. With the possible exception of the new date data types (again, not at the top of most lists of changes) this is the biggest improvement vs. SQL 2005.

For some strange reason, SQL Server's syntax always let you reference a non-existent table without giving you any warning, even though referencing a non-existent column in an existing table gave you a syntax error. Both of these should have been warnings, but should let you create your stored proc anyway, in case the non-existent reference exists at runtime.

What SQL 2008 fixed is it keeps track of the missing dependency - as opposed to early versions which just ignored it, and did not update a reference to it. SQL 2008 is still broken - references can still get out of sync. Before doing anything important, you can updated all the dependencies with:

SELECT
'EXEC sp_refreshsqlmodule N''' + s.[name] + '.' + o.name + ''';' AS [stmt]
, s.[name] AS [schema_name] -- schema name
, o.[name] AS [object_name] -- procedure, function, or view name
, o.[type] AS [object_type] -- type (P, FN, IF, TF, or V)
FROM sys.objects o
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] IN ('FN','IF','TF','P','V')
ORDER BY
o.[type]
, s.[name]
, o.[name]

I apologize if you wrote this script - I did grab this from the Internet without noting the author.


You can then view all your "missing" dependencies by running:

SELECT s.name [schema], o.name, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referencing_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE referenced_id IS NULL
ORDER BY s.name, o.name, d.referenced_entity_name


This is still broken in one more import way - if you have code like:

UPDATE T
SET Price = Price + 1
FROM Table1 T

You will get an unresolved reference to an unknown object 'T' rather than a correct reference to Table1. I believe you can change any update query to a syntax that will work correctly, and you can easily find all the places you used the "problematic" sytnax with the above query.


If you aren't entrenched in SQL, this change may sound trivial, but I have had problems with dependencies since SQL Server 7, and having this fixed is by far the biggest reason for me to use SQL 2008.