Finding SQL Column Dependencies


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

	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)

Temporarily Preventing SSIS flow while debugging

When debugging an SSIS package, sometimes you have a need to only execute a small portion of the package. While you do have the option of disabling elements, this can prove time consuming if you have a lot of them. Although you could remove the constraint all together, this won’t solve the problem because then it will be executed in parallel.

A quick solution is to set an expression which will always evaluate to false, such as 1 > 2.

This will prevent the package from continuing past the constraint and is very easy to undo.