Depend on Dependencies in SQL Server 2008

  • Article

I picked up a number a couple of questions from Thursday night's TechNet Road show in London including a couple on dependencies in SQL Server 2008.  So here's the answers...

Firstly there are two sorts of dependencies:

  • Schema Bound, where the object A cannot be deleted because object B depends on it.
  • Non-Schema Bound, where Object A can be deleted or may not even have been created, however Object B still depends on it.

Dependencies are updated when a referring object is created and rely on names so if you want to rely on this then you need to use proper four part names i.e. MyServer.MyDatabase.MyTable.MyColumn.

Note that you can get dependencies across linked servers but this will only work with the four part naming convention and not EXEC ('…') AT linked_srv1

The next question I got asked was how synonyms work with dependencies. so here's an example:

-- Create a synonym for the Product table in AdventureWorks.
USE AdventureWorksDW
CREATE SYNONYM dbo.MyProduct
FOR AdventureWorks.Production.Product;
GO

-- Create MyView over the synonym
CREATE VIEW dbo.MyView AS
SELECT Name,ProductNumber FROM dbo.MyProduct

-- check the dependency for MyView
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name
, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'dbo.MyProc1');

All this shows is the dependency on the synonym

image 

If a view is created against the table directly i.e.

CREATE VIEW dbo.MyView AS
SELECT Name,ProductNumber FROM AdventureWorks.Production.Product;

..then the dependency reports correctly. 
image

I have to say I don't think that's a great story and you get the same information back from the management console if you right click on an object and select view dependencies.

Technorati Tags: SQL Server 2008,dependencies,SQL