I’m working on a project to rewrite an older SSIS package. As part of that rewrite, I noticed that there were some duplicate columns (with different names) being sent from vendor. They corrected their end, and now I want to drop the extra columns in the database.

Problem is, how can you make sure that those columns weren’t used in some other procedure or view?

I found this article on Different Ways to Find SQL Server Object Dependencies and started with Svetlana’s second example.

Unfortunately it didn’t quite give me what I wanted. Since we heavily use schemas in our database and we don’t use Hungarian notation, I wanted to make sure that I could more easily locate the objects I needed.

I revised the code to include the schema name and object type, as well as pulling the items out into variables for easy reading.

DECLARE @TableName AS VARCHAR(50) = 'Customers' -- Table that holds column you are dropping or renaming
DECLARE @ColumnName AS VARCHAR(50) = 'Cstomer'	-- Column you are looking for

SELECT
	OBJECT_SCHEMA_NAME(referencing_id) + '.' + OBJECT_NAME(referencing_id)	AS referencing_name
,   o.type
,	referenced_database_name
,	referenced_schema_name
,	referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referencing_id = o.object_id
WHERE OBJECT_NAME(d.referenced_id) = @TableName 
    AND OBJECT_DEFINITION (referencing_id)  LIKE '%' + @ColumnName + '%'
ORDER by OBJECT_SCHEMA_NAME(referencing_id), OBJECT_NAME(referencing_id)