How to determine table column dependencies within a SQL database

If changes need to be made to a column it is necessary to perform some impact assessment in order to determine what objects will be affected, meaning that SQL table column dependencies within a SQL Server database need to be found

One of the ways is to use a SYSCOMMENTS table. It is a table which contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The column TEXT in the syscomments table contains the actual code for all these objects, and knowing it you can write a code to check the dependencies:

select name 
from syscomments c 
join sysobjects o on c.id = o.id 
where TEXT like '%Person%' and TEXT like '%FirstName%'

The query above is used to determine which objects use the FirstName column of the Person table. After executing it, the results will be:

This is just a simplified example that contain some basic information. The downside with this method is that it will return all objects that merely mention words “Person” and “FirstName” without actually making a reference to Person.FirstName column

Now let’s check the same scenario and available information in ApexSQL Clean, a tool that can analyze SQL server database dependencies and delete unwanted objects. First thing to do is to activate the Column dependencies option

This option will include all available columns in the main grid making it possible to expand and preview columns for each table. Besides the name of the column, the column type is automatically displayed whether it is a Regular, ROWGUID or Identity type column

For the selected object or column, ApexSQL Clean will provide more additional info in the Children and Parent panes. The Children pane will contain a list of objects that depend on the selected one, in this case the FirstName column

Whenever a table schema is changed, it is important to identify all SQL database objects that might be affected by that change. Without doing a complete impact analysis, there is the risk of causing problems when implementing the update. The main advantage of using ApexSQL Clean is leveraging proprietary dependency analysis algorithms to be able to show column level dependencies, unlike SQL Server Management Studio, where the object level dependencies can only be seen.