I've been attempting to write some SQL code that when provided with a view will locate the columns that the view columns reference and work out if there are any indexes on those columns. The end aim is to provide users with a list of columns it would be possible to use when querying the view.
Currently though I can only find the columns that the view uses (and by extension their indexes) but I cant match them back to the index's columns.
For example:
I have TableA, which has 5 Columns: ID, Name, Val1, Val2, TableBID
I have TableB, which has 3 Columns: ID, Name, Code
I then create a view, View1, which is:
SELECT A.ID,
Name,
Val1
FROM TableA A
INNER JOIN TableB ON A.TableBID = B.ID
WHERE B.Code = 'AAA'
When I query for references using:
SELECT *
FROM sys.dm_sql_referenced_entities('dbo.View1', 'OBJECT')
I'll get a list of the Table/Column references within it, but no indication of which View Column references what.
Is there any way I can access the information I need, bear in mind I cannot do name matching as the columns in the Alias may use aliases and therefore may not have the same names as the underlying data.
I'm using SQL Server 2008 SP1 if that has any impact.