How to know which columns correspond between tables and a view

Question

I've got a view in SQL Server, and would like to join this view with other tables, through my C# application. To accomplish this, I would need to find out which columns the respective view's fields correspond with, in the underlying table. For example, I could have a view like so:

CREATE VIEW [View A]     
AS   
   SELECT Children.Child_ID, Social_Workers.Social_ID 
   FROM Children 
   INNER JOIN Social_Workers 
     ON Children.Social_ID = Social_Workers.Social_ID

I may want to join a table to the above view. To accomplish this, my C# application must somehow know which are the required foreign key and primary key fields within the relationship, thus generating SQL code like so:

SELECT [View A].Child_ID, 
       Sponsors.User_ID 
FROM  [View A]  
INNER JOIN Sponsors 
 ON [View A].Child_ID = Sponsors.Child_ID

I have found a way to retrieve the underlying tables within the view, however I am unsure of how to approach the rest of the problem.

Can you describe the query that you want to run? The field names in the view seem pretty clear. — Gordon Linoff, Mar 07 '13 at 12:36
@GordonLinoff - How would my C# application know which columns in the View are the corresponding columns of the relationship? The views can have different column names to their actual FK and PK counterparts in the real tables. — Dot NET, Mar 07 '13 at 12:45
In the view example you give, the column names are the same in the view as in the underlying tables. Also, I don't understand what you want to do. If you need information about the child, then you use `child_id`. If you need information about the social worker, then you use `social_id`. — Gordon Linoff, Mar 07 '13 at 12:57
You want to dynamically create joins to View based on existing FK, but column names in View may differ from column name in FK? — Danila Polevshchikov, Mar 07 '13 at 12:57

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

If i right understand your requirements, you can try to use queries from Find the real column name of an alias used in a view?

edited May 23 '17 at 12:11

Community

1
1

answered Mar 07 '13 at 12:58

Danila Polevshchikov

2,228
2
24
35

Unfortunately the code example in that accepted answer has a flaw and does not work all the time according to comments. – Dot NET Mar 07 '13 at 13:02
And another answer is "I think you can't". may be using of naming conventions to a view column names will help? – Danila Polevshchikov Mar 07 '13 at 13:04
Unfortunately the application can be used with any views of the user's choice, so I cannot really impose a restriction :/ – Dot NET Mar 07 '13 at 13:07

score 0 · Accepted Answer · answered Mar 11 '13 at 02:38

I think the following might help, specifically the referenced_entity_name and referenced_minor_name columns.

select * from sys.dm_sql_referenced_entities( 'dbo.ViewA', 'object' ) ;

This DMV was added in SQL Server 2008 so if you have an earlier version, no help I'm sorry.

How to know which columns correspond between tables and a view

2 Answers2