|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
to create a table-valued functions for obtaining a list of dependent objects:
USE AdventureWorks2008;
GO
-- DROP FUNCTION fnDependentObjectList
CREATE FUNCTION fnDependentObjectList ( @Objectname sysname)
RETURNS @buffer table (Given sysname, [Dependent] sysname)
AS
BEGIN;
WITH cteDependence (DependentObjectID, DependentObjectName, GivenObjectName, GivenObjectID)
AS
(
SELECT DISTINCT
d.object_id,
OBJECT_NAME(d.object_id),
GivenObject = OBJECT_NAME(d.referenced_major_id),
GivenObjectID = d.referenced_major_id
FROM
sys.sql_dependencies d
JOIN sys.objects so
ON d.referenced_major_id = so.object_id
WHERE
d.referenced_major_id = object_id(@ObjectName)
UNION ALL
SELECT
d.object_id,
OBJECT_NAME(d.object_id),
OBJECT_NAME(referenced_major_id),
object_id
FROM
sys.sql_dependencies d
JOIN cteDependence dep
ON d.referenced_major_id = dep.DependentObjectID
WHERE
d.referenced_major_id <> d.object_id
)
INSERT @buffer
SELECT DISTINCT
GivenObjectName, DependentObjectName
FROM
cteDependence d
RETURN
END
GO
SELECT * FROM fnDependentObjectList ('Production.Product')
GO |
