question

takeiteasyn20 avatar image
takeiteasyn20 asked

metadata from view and other objects

Hello,

Is there a way to extract an end-to-end lineage from the meta data in sql server? for e.g. all views that use columns from synonyms in SELECT (with alias decoded) /columns in WHERE clause?

best,

GS

meta-data
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

takeiteasyn20 avatar image
takeiteasyn20 answered

Thank you @Jon Crawford, I had checked this article before however it gives us the nested dependencies.

What I was looking for is for e.g. Underlying / base columns of all the columns returned by the view i.e. alias decoded and any columns used by the WHERE clause.

sys.dm_sql_referenced_entities does the job well for attributes in SELECT stmt but not when there are synonymns or Where columns conditions.

The output format I am requesting is (for a view)

referencing object | ro_typ | ro_col (alias) | underlying object | uo_typ | uo_column

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

I think you're asking too much there. You're always going to find some sort of gap, you're going to have to decide what your threshold for "good enough" is going to be. You can look here for synonyms,

SELECT *

FROM sys.synonyms

but what if someone is using dynamic SQL to build strings from reference tables? That's runtime code based on inputs, so you won't really know that.

At some point, you're going to have to rely on manual documentation to be 100% accurate.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

takeiteasyn20 avatar image
takeiteasyn20 answered

Thank you so much @Jon Crawford. I agree to your dynamic sql point. I have made an ambitious attempt in writing something that gives everything except it using dm_dependencies, describe_first_result_set and sys.modules.[definition] and it worked well.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.