Share this post

This table function return all the dependencies for a table/column.

The source used is here : http://www.sqlusa.com/bestpractices2008/object-dependency/

-- =============================================
-- Description:	Find all the dependencies for an Entity/column
--		Parameter Entity : [Optional] the name of the table or view
--								the name must be passed like this : dbo.TableName
--		Parameter Column : [Optional] the name of the column
-- =============================================
CREATE Function [dbo].[udf_Dependecies] (@Entity varchar(max), @Column varchar(max))
RETURNS @retTable TABLE 
(
    ReferencingObject nvarchar(max) NULL,
    ReferencedObject nvarchar(max) NULL,
    ColumnName nvarchar(max) NULL,
    ReferencedObjectType nvarchar(10) NULL,
    ReferencingObjecType nvarchar(10) NULL
)
AS
BEGIN
INSERT INTO @retTable(ReferencingObject, ReferencedObject, ColumnName, ReferencedObjectType, ReferencingObjecType) 
	SELECT	ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
			ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.' + ed.referenced_entity_name,
			ColumnName = c.name,
			ReferencedObjectType = o2.type,
			ReferencingObjecType = o1.type
	FROM   sys.sql_expression_dependencies ed
	INNER JOIN sys.objects o1 ON ed.referencing_id = o1.object_id
	INNER JOIN sys.objects o2 ON ed.referenced_id = o2.object_id
	INNER JOIN sys.sql_dependencies d ON ed.referencing_id = d.object_id AND d.referenced_major_id = ed.referenced_id 
	INNER JOIN sys.columns c ON c.object_id = ed.referenced_id AND d.referenced_minor_id = c.column_id
	WHERE	(IsNull(@Entity,'') = '' OR SCHEMA_NAME(o2.schema_id) + '.' + ed.referenced_entity_name = @Entity)
		AND	(IsNull(@Column,'') = '' OR c.name = @Column)
	ORDER  BY ReferencedObject, c.column_id

RETURN
END