sql server - Find Column dependency

Question

How to find objects which depend on particular column of table.

Ex:

Table: SomeTable

Cols: col1 pk, col2, col3

I want to find all the objects which are dependent on col1 (Pk)

Answer 1

@NoFuchsGavin's script usually works great but has some limitations due to issues with sysdepends (see this blog post by Pinal Dave for an example where this gives incorrect results).

Microsoft also suggest that you avoid using sysdepends in new development work.

We can therefore use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities as suggested here.

However I've noticed that this sometimes excludes column references due to referenced_minor_name being NULL. I've therefore added another condition which can introduce false positives but ensures that column references are not omitted from the result set.

DECLARE @SchemaName sysname = '{0}';
DECLARE @TableName sysname  = '{1}';
DECLARE @ColumnName sysname = '{2}';

SELECT
    @SchemaName + '.' + @TableName                                      AS [USED_OBJECT],
    @ColumnName                                                         AS [COLUMN],
    referencing.referencing_schema_name + '.' + referencing_entity_name AS USAGE_OBJECT,
    CASE so.type
        WHEN 'C' THEN 'CHECK constraint'
        WHEN 'D' THEN 'Default'
        WHEN 'F' THEN 'FOREIGN KEY'
        WHEN 'FN' THEN 'Scalar function' 
        WHEN 'IF' THEN 'In-lined table-function'
        WHEN 'K' THEN 'PRIMARY KEY'
        WHEN 'L' THEN 'Log'
        WHEN 'P' THEN 'Stored procedure'
        WHEN 'R' THEN 'Rule'
        WHEN 'RF' THEN 'Replication filter stored procedure'
        WHEN 'S' THEN 'System table'
        WHEN 'SP' THEN 'Security policy'
        WHEN 'TF' THEN 'Table function'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'U' THEN 'User table' 
        WHEN 'V' THEN 'View' 
        WHEN 'X' THEN 'Extended stored procedure'
    END                                             AS USAGE_OBJECTTYPE,
    so.[type]                                       AS USAGE_OBJECTTYPEID
FROM sys.dm_sql_referencing_entities
    (
        @SchemaName + '.' + @TableName,
        'object'
    ) referencing
    INNER JOIN sys.objects so 
        ON referencing.referencing_id = so.object_id
WHERE
    EXISTS
    (
        SELECT
            *
        FROM
            sys.dm_sql_referenced_entities
            (
                referencing_schema_name + '.' + referencing_entity_name,
                'object'
            ) referenced
        WHERE
            referenced_entity_name = @TableName
            AND 
            (
                referenced.referenced_minor_name LIKE @ColumnName   
                -- referenced_minor_name is sometimes NULL
                -- therefore add below condition (can introduce False Positives)
                OR
                (
                    referenced.referenced_minor_name IS NULL 
                    AND 
                    OBJECT_DEFINITION
                    (
                         OBJECT_ID(referencing_schema_name + '.' + referencing_entity_name)
                    ) LIKE '%' + @ColumnName + '%'
                )
            )
    )
ORDER BY
    USAGE_OBJECTTYPE,
    USAGE_OBJECT

Above script is based on @NoFuchsGavin's answer and this blog post.

I'm interested to know if anyone has managed to find a better way which does not introduce false negatives or positives.