Finding References To Particular Columns In Procedures And Views

Here’s the scenario:  you want to find all of the cases in which a particular column name gets used.  Here’s the simple version, built off of sys.sql_modules:

declare @TableName sysname = 'MyTable';
declare @ColumnName sysname = 'MyColumn';

select
	object_schema_name(object_id) as SchemaName,
	object_name(object_id) as TableName,
	definition
from
	sys.sql_modules
where
	definition like '%' + @TableName + '%'
	and definition like '%' + @ColumnName + '%';

This works just fine if you have a unique column name or you’re willing to wade through a number of false positives.  In my case, though, I had to do this on a common column name and often-used table, so that there were well over 100 results.  In this case, it’s time to pull out the big guns:  dm_sql_referencing_entities and dm_sql_referenced_entities.  These are two DMFs which take a couple of parameters:  schema+object name, and type.  Here is a query which looks at all modules (views, functions, procedures, triggers, etc.) and returns the items which reference @SchemaName.@TableName.@ColumnName:

declare @SchemaName sysname = 'dbo';
declare @TableName sysname = 'MyTable';
declare @ColumnName sysname = 'MyColumn';

select
	referencing.referencing_schema_name + '.' + referencing_entity_name as CallingProcess,
	so.type,
	so.type_desc
from
	sys.dm_sql_referencing_entities(@SchemaName + '.' + @TableName, 'object') referencing
	inner join sys.objects so on referencing.referencing_id = so.object_id
where
	referencing.referencing_entity_name not in
	(
		'List Broken Objects Here'
	)
	and 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 = @ColumnName
	)

This procedure first gets all of the referencing entities for @SchemaName.@TableName.  That is, what modules (views, procedures, triggers, check constraints, etc.) reference @SchemaName.@TableName anywhere in their definition?  From there, we perform a couple of filters.  The important filter is the “and exists” clause.  What we’re doing is getting all of the referenced entities for each of those referencing entities.  The reason we have to do this is that sys.dm_sql_referencing_entities keeps the column name in a field called referenced_minor_name.  Because we’re looking for a particular column, we need to get that, and it’s not enough simply to look for references to our schema and table.

Above that I have a snippet which filters out bad rows.  The problem is that procedures and views which are not schemabound are allowed to have bad references.  When you try to get the referenced entities, you’ll end up with an error like:

Msg 2020, Level 16, State 1, Line 1
The dependencies reported for entity “dbo.MyBrokenprocedure” might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

If you get that, there’s a good chance that you might need to rewrite (or remove) that module because it isn’t working. 

Caveats:

• There are exceptions to the statement above.  For example, suppose that your procedure uses a linked server but your account does not have permission to access that linked server connection.  In that case, you’ll see the same error even though the procedure itself may work correctly when the correct person runs it.
• Columns do not appear in the sys.dm_sql_referenced_entities DMF when using linked server connections.  You can see the tables, but no further.
• This method really is designed to work within a single database, not to span across databases.
• This method might not get all of the references.  I did notice a case in which a procedure did not have columns show up even though they definitely were used.  It might have had to do with the columns only being used in a WHERE clause or not being part of the final select/insert statement, but I’m not positive.

 

Advertisements