Using T-SQL to identify columns used in joins in view definitions

Question

let's say you have defined a view according to:

CREATE VIEW v_name 
AS
SELECT * FROM a
JOIN b ON a.col1 = b.col2

Would it be possible to use a query, function or stored procedure to obtain the columns that were joined?

I understand that there are some options that allow for obtaining the tables, using sys.sql_expression_dependencies, sys.views or sys.dm_referenced_entitities(), but they do not allow you to obtain more meta-data than the table that was used.

From T-SQL, you'd be looking at obtaining the XML execution plan from the cache and then hunting for the `join` nodes -- it's doable but far from easy. You have more convenient options from client code, as there's a full-featured T-SQL parser in .NET (see, e.g., [this](https://stackoverflow.com/a/27305052/4137916)), and you can search references to arbitrary columns in [SSDT](https://learn.microsoft.com/sql/ssdt/download-sql-server-data-tools-ssdt) database projects. — Jeroen Mostert, Jan 09 '20 at 14:19

score 0 · Answer 1 · answered Jan 09 '20 at 14:56

Not the complete answer but helpful to begin with.

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, SUBSTRING(VIEW_DEFINITION, CHARINDEX('join', VIEW_DEFINITION, 1), LEN(VIEW_DEFINITION)) AS 'View_Join'
FROM INFORMATION_SCHEMA.VIEWS 
WHERE VIEW_DEFINITION like '%JOIN%ON%'

Using T-SQL to identify columns used in joins in view definitions

1 Answers1