People reading this post might really wonder of having read /heard about a concept on Dependency tracking in SQL Server 2008 . It is nothing, but a set of built in existing SYS objects and their effective usage that help us save time & tedious efforts.
I personally am habituated to using sp_help , sp_helptext, sys.tables, sys.indexes etc. and many other built in DB Objects as Keyboard shortcuts. It is always a good practice to look into the references/dependencies/keys/DataTypes/Constraints/relationships(parent-child) of DB objects that contain taxonomy data even before we start writing code that could be to write a SELECT * query with joins to conducting extensive ‘ISUD’ or batch transactions.
It hasn’t been too late 
...that I discovered some cool SYS objects in SQL Server 2008 that have made me save time as well as analyze my DB better. Let me just give a quick run of some new built in sys objects in SQL Server 2008.
sys.sql_expression_dependencies
This is a New catalog view. It replaces sys.sql_dependencies in previous versions of SQL Server 2K/2K5. The Utility helps us to track both schema-bound and non-schema-bound dependencies as well as track cross-database and cross-server references (by name)
SYNTAX : SELECT *
FROM sys.sql_expression_dependencies
sys.dm_sql_referenced_entities
This is a new dynamic management function introduced in SQL Server 2008.It replaces sp_depends in previous versions of SQL Server 2K/2K5. It basically returns a row for each entity referenced by a given entity.
For Example, It is extensively used to display objects referenced in bulky stored procedure. People who have done this task this manually would have gone through various levels of the stored procedure to figure out the references and obviously the fforts involved were tedious and time consuming.
SYNTAX : SELECT *
FROM sys.dm_sql_referenced_entities('dbo.ISUDProcedure', 'OBJECT')
sys.dm_sql_referencing_entities
This is a new dynamic management function introduced in SQL Server 2008.It replaces sp_depends in previous versions of SQL Server 2K/2K5. It basicall returns a row for each entity referenced by a given entity.
For Example, I need to find out all DB Objects/references that will be broken when I drop a Table or even modify a table by adding a new column/dropping a column in the table. It is very important to identify such aspects as it could end up as a potential risk and eventually incoming data could be scattered.
SYNTAX : SELECT *
FROM sys.dm_sql_referencing_entities('dbo.FragileTable', 'OBJECT')
SAMPLE PLAY
IF DATABASEPROPERTY('ProductCatalog', 'Version') IS NOT NULL
DROP DATABASE ProductCatalog
GO
CREATE DATABASE ProductCatalog
GO
USE ProductCatalog
GO
-- STEP1
-- Checking for schema-bound and non-schema-bound dependencies as well
-- as cross-database and cross-server references (by name)
SELECT *
FROM sys.sql_expression_dependencies
--Creating some DB Objects
CREATE TABLE [dbo].Product
(
ProductID INT
, [Name] VARCHAR(20)
)
GO
CREATE TABLE [dbo].Orders
(
ProductID INT
, OrderID INT
, Description VARCHAR(20)
)
GO
-- Populate the tables with some data
---------------------------------------------
CREATE PROCEDURE [dbo].spc_GetProductDetail
AS
SELECT ProductID
, [Name]
FROM [dbo].Product;
GO
CREATE PROCEDURE [dbo].spc_UpdateProductDetail
AS
UPDATE [dbo].Product
SET [Name] = 'Olay'
WHERE ProductID = 1;
GO
---------------------------------------------
CREATE VIEW Vw_ProductOrder
WITH SCHEMABINDING
AS
SELECT p.ProductID
, p.Name
, o.Description
FROM [dbo].Product p
INNER JOIN [dbo].Orders o
ON p.ProductID = o.ProductID
GO
---------------------------------------------
CREATE SCHEMA ProductSchema;
GO
CREATE FUNCTION ProductSchema.Analysis(@x INT)
RETURNS TABLE AS
RETURN
SELECT ProductID
, [Name]
FROM [dbo].Product
WHERE ProductID < @x;
GO
---------------------------------------------
SELECT *
FROM sys.dm_sql_referencing_entities ('dbo.Product', 'OBJECT');
GO
SELECT *
FROM sys.dm_sql_referenced_entities('dbo.spc_UpdateProductDetail', 'OBJECT')
GO
Check this out!