SQL: how to find a mapping of view columns to table columns?

Question

In Microsoft SQL Server 2008 R2, let's say my database has the following view:

    create view [dbo].[MyView]
    (
        [MyColumnA]
    )
    AS
    (SELECT MyColumnB FROM MyTable)

Now let's suppose I only know that there is a view called MyView that has a column called MyColumnA, but I don't know that it maps to MyTable.ColumnB. What is the easiest/fastest way to determine which table and column MyView.ColumnA maps to? Is there a query that can tell me this? Something like:

    SELECT TABLE_NAME, TABLE_COLUMN_NAME 
    FROM INFORMATION_SCHEMA.VIEW_MAPPINGS 
    WHERE VIEW_NAME = 'MyView' AND VIEW_COLUMN_NAME = 'MyColumnA'

This query would return [MyTable, MyColumnB].

Currently I have to find the view in SSMS Object Explorer, right click it and generate the create script, then search for the name of the view's column. Then I note which ordinal position it is in the view (let's say 4th column), and have to find the corresponding 4th column in the select statement. The select statement will most likely be using a table alias, so then I have to look through the JOIN statements to find the table name based on the alias.

This is quite time consuming, and I'm hoping to find a faster way, if not by a query then perhaps by some other process that is faster or easier than mine.

See http://stackoverflow.com/questions/10048056/find-the-real-column-name-of-an-alias-used-in-a-view — Turophile, May 22 '14 at 22:17

score 3 · Answer 1 · answered May 22 '14 at 22:30

3

SP_DEPENDS should work

SP_DEPENDS 'MyView'

answered May 22 '14 at 22:30

Santhosh_ms3

110
2
3
15

This kind of works - it does show which columns map into a view somewhere, but I doesn't show the actual mapping, which is what the OP (and me) are looking for. I wish there was a way to force the view definition you generate to be in the "SELECT W as X, Y as Z, etc" layout rather than the layout the OP shows above, but it doesn't seem possible. – SqlRyan Apr 12 '16 at 18:24
@SqlRyan Yeah. I am looking for exactly the same thing. Please let me know if you find a way. – Pouya BCD Jan 22 '18 at 22:13
@PouyaBCD Yeah, I never did find an answer to this one but still hopeful... four years later :) – SqlRyan Mar 03 '18 at 22:14

SQL: how to find a mapping of view columns to table columns?

1 Answers1