CODEWITHSUNDEEP
sql-serverforeign-keysforeign-key-relationshipdatabase-metadata
Tony O'Hagan
Tony O'Hagan

Reputation: 22682

Can I find the foreign key relations for a view in SQL Server?

I've built a tool that uses table foreign keys but I'd like to extend it to support views.

Upvotes: 0

Views: 138

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

In SQL Server 2008 and above, assuming you are only interested in dependencies within the same database, you can use the following query to determine all of the tables, views, and functions referenced within each view:

;WITH x AS
(
 SELECT
  v = QUOTENAME(OBJECT_SCHEMA_NAME(d.referencing_id))
     + '.' + QUOTENAME(OBJECT_NAME(d.referencing_id)),
  t = QUOTENAME(OBJECT_SCHEMA_NAME(d.referenced_id))
     + '.' + QUOTENAME(OBJECT_NAME(d.referenced_id)),
  td = o2.type_desc
 FROM sys.sql_expression_dependencies AS d
 INNER JOIN sys.objects AS o1
 ON d.referencing_id = o1.[object_id]
 INNER JOIN sys.objects AS o2
 ON d.referenced_id = o2.[object_id]
 WHERE o1.type_desc = 'VIEW'
)
SELECT [View] = v, Referenced_Object = t, [Type] = td
 FROM x
 GROUP BY v, t, td
 ORDER BY v, t;

Upvotes: 2

Related Questions