Column Lineage in SQL

Hey,

Based on my understanding unfortunately directly via TSQL getting end to end column lineage is not possible.

It has to be manually validated or use some 3rd party tools.

Reason : in case if column has alias or is being used in some case/calculaed logics, we need to further track those columns lineage as well.

So for end to end lineage, directly is not possible as per my 2 cents worth

Check this:

SELECT DISTINCT
	s.name AS schema_name,
    o.type_desc AS object_type,
    o.name AS object_name
FROM
    sys.sql_modules m
INNER JOIN
    sys.objects o ON m.object_id = o.object_id
INNER JOIN
    sys.schemas s ON o.schema_id = s.schema_id
WHERE
    m.definition LIKE '%table1%Col1%'
    AND (o.type = 'P' OR o.type = 'V' OR o.type = 'FN');

Yes, 

Please follow this

USE YourDatabaseName;

DECLARE @ColumnName NVARCHAR(100) = 'YourColumnName';
DECLARE @TableName NVARCHAR(100) = 'YourTableName';

-- Find references in stored procedures, views, and functions
SELECT 
    referencing_object.name AS ReferencingObject,
    referencing_object.type_desc AS ObjectType
FROM 
    sys.sql_expression_dependencies AS sed
JOIN 
    sys.objects AS referencing_object ON sed.referencing_id = referencing_object.object_id
JOIN 
    sys.columns AS referencing_column ON sed.referencing_minor_id = referencing_column.column_id
WHERE 
    referenced_entity_name = @TableName
    AND referenced_minor_name = @ColumnName;