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)