0

Is there a way to get from SQL Server metadata information about the dependencies of a view, in which manner are linked together (inner join, left join, right join) and the join's keys? (that_table.Id = my_table.That_tableId AND X.Id = Z.XId) ?

I've found:

SELECT * FROM sys.sql_expression_dependencies WHERE referencing_id = object_id('dbo.v_myView')

but is very far from my goal.

edit: For example: this is the View:

CREATE VIEW [dbo].[MyView]
AS
  SELECT A.NAME
         B.AGE
         C.SURNAME
  FROM TABLE1 A 
  INNER JOIN TABLE2 B
  ON A.ID1 = B.Table1_key1 AND A.ID2 = B.Table1_Key2
  LEFT JOIN TABLE3 C
     ON C.ID = A.Table3Id

this is the expected result from the query i'm looking for

id object_name dependecies RefParent typeOfJoin keys
1 MyView table1 null null null
2 MyView table2 1 inner join A.ID1 = B.Table1_key1 AND A.ID2 = B.Table1_Key2
3 MyView table3 1 left join C.ID = A.Table3Id

Thank you in advance.

Carlo Salaroglio
  • 11
  • 1
  • 4
  • What do you mean with `dependency of a view` ? – GuidoG May 11 '22 at 09:30
  • If you execute the query i posted, you should see a column "referenced_entity_name". In that column from my understanding, you are getting the tables used in the select to compose the result. So in my understanding, the tables used in the select are dependencies of a view – Carlo Salaroglio May 11 '22 at 09:33
  • Can you post an example of a view and what exact you whish to see as result – GuidoG May 11 '22 at 09:36
  • I am not sure this will be so easy, in the view you can join on whatever columns you want, even columns that are not in a foreign key, and I believe the system tables only list relations that are defined in foreign keys, primary keys and indexes. – GuidoG May 11 '22 at 09:44
  • I guess all you can do is retrieve the code of the view from `select table_name, view_definition from INFORMATION_SCHEMA.VIEWS where table_name = 'dbo.v_myView'` and then parse it to figure out all the joins that where made there – GuidoG May 11 '22 at 09:49
  • 1
    I think you would need an SQL parser/exer for this. There is no information in the system view that shows the info you want – Charlieface May 11 '22 at 10:16

1 Answers1

-1
SELECT * FROM INFORMATION_SCHEMA.VIEWS

is probably what you're looking for?

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE might also help.

JonTout
  • 618
  • 6
  • 14
  • The `INFORMATION_SCHEMA.VIEW_COLUMN_USAGE` shows columns that are used as foreign key or primary key, not columns that are used in code of a view. So I am afraid to is useless for the OP. In a view you can write joins on any table and any column you want, none of those will end up in this table or any other table – GuidoG May 11 '22 at 09:47
  • INFORMATION_SCHEMA.VIEW_COLUMN_USAGE shows all of the columns in the view, tables used, schema - which is why it "might help" https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/view-column-usage-transact-sql?view=sql-server-ver15 – JonTout May 12 '22 at 14:46