Meta data identification

Question

Hello Team,
We have a scenario wherein we need to identify where all a column of a particular table is being used and which all tables would be impacted from the same.
eg: col 1 of table t1 is being dropped

Scenarios:

1. there is a stored procedure which populates table t2 and within that stored procedure, column c1 of table t1 is being used.

eg:
insert into t2 (c2)
Select
c1 as 'c2'
from t1

Now, verify column dependency via SSMS would only provide me the objects names like table t1, Stored procedure or those tables having PK-FK relationships .

but it wont provide me that table t2 would also be impacted dynamically.
We have to manually go and analsye the stored procedure.

So is there any automated way or any tool via which we can get the required results

Answer 1

Hi - if you are looking at dependencies within a single database, the system stored procedure sp_depends could be helpful.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-ver15

If you have any dynamic sql running, you should be extra cautious.

Redgate provide a graphical dependency tracker, with a free trial. I highly rate red Gate’s products, but haven’t used this personally
https://www.red-gate.com/products/sql-development/sql-dependency-tracker/

Please let me know if this helps. Dependency issues can be a pain in legacy systems, and should definitely be a consideration when designing new solutions.