/*============================================================================
  File:     simple_dependencies.sql

  Summary:  Illustrates the new dependency tracking DMVs

  Date:     August 2008

  SQL Server Version: 10.0.1600.22 (RTM)
------------------------------------------------------------------------------
  Written by Bob Beauchemin, SQLskills.com

  For more scripts and sample code, check out http://www.SQLskills.com

  This script is intended only as a supplement to SQL Server Jumpstart program.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Dependency tracking

if DATABASEPROPERTY('test', 'Version') is not null
DROP DATABASE test
GO

CREATE DATABASE test
GO

USE test
GO

-- New catalog view; replaces sys.sql_dependencies
-- Tracks both schema-bound and non-schema-bound dependencies
-- Tracks cross-database and cross-server references (by name)
select * from sys.sql_expression_dependencies

-- New dynamic management function; replaces sp_depends
-- Returns a row for each entity referenced by a given entity
-- E.g. show me all objects referenced in stored procedure p1
select * from sys.dm_sql_referenced_entities('dbo.p1', 'OBJECT')

-- New dynamic management function; replaces sp_depends
-- Returns a row for each entity that references a given entity
-- E.g. show me all objects that would be broken if I drop table t1
select * from sys.dm_sql_referencing_entities('dbo.t1', 'OBJECT')

CREATE TABLE dbo.t1 (id int, name varchar(20))
GO

CREATE TABLE dbo.t2 (id int, address varchar(20))
GO

CREATE PROCEDURE dbo.p2
AS
SELECT * FROM t1; 
GO

CREATE PROCEDURE dbo.p3
AS
UPDATE dbo.t1 SET name = 'Fred'
 WHERE id = 1;
SELECT id, name FROM dbo.t3 -- Doesn't exist
go

CREATE VIEW v1 
WITH SCHEMABINDING
AS
SELECT t1.id, t1.name, t2.address 
FROM dbo.t1 
INNER JOIN dbo.t2 ON t1.id = t2.id
GO

CREATE SCHEMA s;
GO

CREATE FUNCTION s.foo (@x INT) 
RETURNS TABLE AS
RETURN
   SELECT * FROM t1 WHERE id < @x;
go

SELECT * FROM sys.dm_sql_referencing_entities ('dbo.t1', 'OBJECT');
go

-- this produces error 2020 
-- dbo.p3 refers to table dbo.t3
-- which does not exist
SELECT * FROM sys.dm_sql_referenced_entities('dbo.p3', 'OBJECT')
go

-- execution will fail
exec dbo.p3
go

-- now create dbo.t3
create table dbo.t3 (id int, name varchar(20))
go

-- no message 2020
SELECT * FROM sys.dm_sql_referenced_entities('dbo.p3', 'OBJECT')
go 

-- drop a column that the procedure depends on
alter table dbo.t3 drop column name;
go

-- error 2020 and additional message
SELECT * FROM sys.dm_sql_referenced_entities('dbo.p3', 'OBJECT')
go 

ALTER TABLE dbo.t3 ADD name varchar(20)
go

SELECT * FROM sys.dm_sql_referenced_entities('dbo.p3', 'OBJECT')
go 

-- old metadata
select * from sys.sql_dependencies
go

-- new metadata
select * from sys.sql_expression_dependencies
go


