0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tony O'Hagan
  • 21,638
  • 3
  • 67
  • 78
  • 1
    How are you creating foreign keys on or against a view? Do you mean dependencies? If so, what version of SQL Server? – Aaron Bertrand Apr 19 '12 at 00:55
  • We typically use SQL Server 2008. I'd need to determine the foreign key relations of a view and then generate code to perform the join to other tables or views selected by the user (I do this currently - but just for tables). So I assume I'd need to identify the originating table/column for each view/column (assuming the column is not computed). – Tony O'Hagan Apr 19 '12 at 01:22
  • Yeah, that's not going to be trivial at all. – Aaron Bertrand Apr 19 '12 at 01:27
  • BTW ... I'm doing this in C# so I have SMO API. – Tony O'Hagan Apr 19 '12 at 01:29
  • I suspect that I may end up having to parse the view sql. – Tony O'Hagan Apr 19 '12 at 01:30
  • Yes, possibly. I think the dependencies view will not reliably map to the base columns. Will it be enough to correlate the foreign keys that you already know exist, and are associated with tables that are referenced by a view? – Aaron Bertrand Apr 19 '12 at 01:49
  • I don't think so ... Currently the user selects a table and can then select related tables. The app then adds the new table (as a tree view child node) and allows the user to see the related data by generating a query using the implied FK join. To substitute views for tables, I need to be able to (a) find related views to a previously selected view or table (b) generate the join condition when they select a view or table. The metadata exists for TABLE <=> TABLE but I suspect is incomplete for VIEW <=> TABLE or VIEW <=> VIEW unless I can correlate VIEW relations down to the column level. – Tony O'Hagan Apr 19 '12 at 03:01

1 Answers1

2

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490