Add option to find dependencies of a particular column
It should be possible to find dependencies of a particular table column.
Hans Jørgen Pedersen commented
Another extension of this idea: I need to filter on a particular column, so ONLY the dependencies on THAT column are shown.
My task is to track down all the places a given data field is used, because the source is compromised and I need to build a business plan on its current use in order to motivate the cost associated with finding a new data source, onboard it, and make the corresponding changes.
Another extension of this idea, to be able to see witch objects are responsible for updating/inserting to a column, why not use arrows:)
-- FIND DEPENDENCY OF A COLUMN IN A TABLE and MATCH IT WITH WHEN THE Object
DECLARE @ColumnName AS SYSNAME = 'LastningsDatum'
DECLARE @TableName AS SYSNAME ='Objekt'
SELECT @TableName [Target Table],
@ColumnName [Target Column],
OBJECT_NAME(sd.id) [Depending object] ,
sd.resultobj [Dependent by Update] ,
sd.readobj [Dependent by Select],
create_date, modify_date, type_desc
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
INNER JOIN sysdepends sd ON so.id = sd.depid
AND sc.colid = sd.depnumber
INNER JOIN sys.objects ON sys.objects.object_id = sd.id
WHERE so.id = OBJECT_ID(@TableName)
AND sc.name = @ColumnName
ORDER BY sd.resultobj DESC , modify_date desc
An extension of this idea - to filter column dependencies based on dependency type.
e.g. column belongs to one of:
root table in a query
inner join on table
left outer join on table
right outer join on table
This would be useful when trying to analyse / refactor a database design.