question

NandanGajananHegdeg-7176 avatar image
0 Votes"
NandanGajananHegdeg-7176 asked ·

Meta data identification

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

azure-sql-database
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@NandanGajananHegdeg-7176

Just checking in to see if the answer(s) helped.

If you found a response helpful, please “Accept Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread.
And, if you have any further queries do let us know. 

0 Votes 0 · ·

1 Answer

FelixP-2774 avatar image
0 Votes"
FelixP-2774 answered ·

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

https://docs.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.

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.