SQL Server Column Dependencies without Deprecated SYS View

Question

I am gathering information about dependencies from "objects" down to the column level (e.g. stored procedure X uses Table A columns C0 and C1).

I have found that my SQL Server 2012 database gives me column-level dependency information via sys.sql_dependencies (because column referenced_minor_id is not identically zero) and it does NOT give me column-level dependency information via sys.sql_expression_dependencies (because column referenced_minor_id IS identically zero).

The docs say sys.sql_dependencies is deprecated in favor of sys.sql_expression_dependencies however it looks as if the "newer" view does not provide column level info (even though it appears to have a column for that purpose).

My conclusion is that in some future version of SQL Server I will be unable to get column level dependency information. Is my conclusion incorrect due to some piece of information I haven't mentioned?

Answer 1

You can get that information but with more work. From the sys.sql_expression_dependencies documentation:

You can use this catalog view to report dependency information for the following entities:

• [other entities]

• Column-level dependencies on schema-bound entities. Column-level dependencies for non-schema-bound objects can be returned by using sys.dm_sql_referenced_entities.

So you get column information in that view for schema-bound entities (for example a calculated column), but for non-schema-bound entities (like for example regular views) you need to combine it with sys.dm_sql_referenced_entities:

select referencing_id, objects.name as referencing_name,
  dependencies.referenced_id, dependencies.referenced_entity_name as referenced_name,
  entities.referenced_minor_id as column_id, entities.referenced_minor_name as column_name,
  is_schema_bound_reference
from sys.sql_expression_dependencies as dependencies
join sys.objects as objects on object_id=referencing_id
join sys.schemas as schemas on schemas.schema_id=objects.schema_id
cross apply sys.dm_sql_referenced_entities(schemas.name+'.'+objects.name,'OBJECT') as entities
where entities.referenced_entity_name=dependencies.referenced_entity_name
  and (is_schema_bound_reference=0 or entities.referenced_minor_id=dependencies.referenced_minor_id)

This query would return column information for both bound and non-bound entities, and also for entities that don't have columns, like a scalar function (table triggers are also included in the OBJECT type).

As a final note, using the schema name in the first parameter of sys.dm_sql_referenced_entities seems to be required although the syntax documentation shows it as optional (I didn't get any results using only the object name in SQL Server 2008, 2012 and 2014).