CODEWITHSUNDEEP
sql-serverdatabase-metadata
Carlo Salaroglio
Carlo Salaroglio

Reputation: 11

Getting metadata from Views from Microsoft SQL Server

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.

Upvotes: 0

Views: 395

Answers (1)

JonTout
JonTout

Reputation: 640

SELECT * FROM INFORMATION_SCHEMA.VIEWS

is probably what you're looking for?

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE might also help.

Upvotes: -1

Related Questions