title: T-SQL Get Table Column based on View Column
body: <p>I have a SQL View that joins a few different tables, and in some cases alters the returned column [EDIT, not names] <strong>values</strong>. The application that uses this dataset needs to occasionally update one or more columns in the views underlying tables. I have a simple fictitious sample to illustrate this….</p>
  
  <p><strong>Table: Lead</strong></p>
  
  <ul>
  <li>Columns: Id, Date, CustId, SalesId</li>
  </ul>
  
  <p><strong>Table: Person</strong></p>
  
  <ul>
  <li>Columns: Id, First, Last, email</li>
  </ul>
  
  <p><strong>Table: Address</strong></p>
  
  <ul>
  <li>Columns: Id, Line1, Line2, City, State, Zip</li>
  </ul>
  
  <p>Then a view something like this…</p>
  
  <pre><code>Create view uvw_LeadActivity
  As
  Select
      L.Id as ‘LeadId’,
      C.Id as ‘CustomerId’,
      C.Last as ‘Customer.LastName’,
      C.First as ’Customer.FirstName’,
      C.email as ’Customer.Email’,
      A.Id as ‘AddressId’,
      A.Line1 as ‘Address1’,
      A.Line2 as ‘Address2’,
      A.City, 
      A.State,
      A.Zip,
      S.Id as ‘SalesId’,
      S.Last as ‘Sales.LastName’,
      S.First as Sales.FirstName’,
      S.email as Sales.Email’,
      L.Date
  From
      Lead L
      Inner join Person C on C.Id = L.CustId
      Inner join Person S on S.Id = L.SalesId 
  </code></pre>
  
  <p>In the application I have the view column name, the value, and an indicator if the value was altered. I'd like to send up the view column name and the value that has changed. So if the user updated the Customer.Email column, I need to be able to figure out that table and column the view column came from to update it.</p>
  
  <p>I feel like I am close, but I am missing something, I hope simple. I have this SQL statement I was using to try and get the table and column that the view is mapped to...</p>
  
  <pre><code>SELECT  
      v.object_Id VIEW_ID,
      v.name AS VIEW_NAME,
      t.object_id AS TABLE_ID,
      t.name AS TABLE_NAME,  
      c.name AS COLUMN_NAME,
      c.column_id AS COLUMN_ID
  FROM  
   sys.views v
   JOIN sys.sql_dependencies d ON d.object_id = v.object_id  
   JOIN sys.objects t ON t.object_id = d.referenced_major_id  
   JOIN sys.columns c ON c.object_id = d.referenced_major_id AND c.column_id = d.referenced_minor_id  
  WHERE  
   v.name='[VIEWNAME]'
  </code></pre>
  
  <p>My results look something like this (condensed):</p>
  
  <pre><code>VIEW_ID     VIEW_NAME           TABLE_ID    TABLE_NAME  COLUMN_NAME COLUMN_ID
  1703311661  uvw_LeadActivity    199671759   Lead        Id          1
  1703311661  uvw_LeadActivity    199671760   Person      Id          1
  1703311661  uvw_LeadActivity    199671760   Person      Last        2
  1703311661  uvw_LeadActivity    199671760   Person      First       3
  </code></pre>
  
  <p>I really want it too look something like this (condensed):</p>
  
  <pre><code>VIEW_ID     VIEW_NAME           VIEW_COLUMN         TABLE_ID    TABLE_NAME  COLUMN_NAME COLUMN_ID
  1703311661  uvw_LeadActivity    LeadId              199671759   Lead        Id          1
  1703311661  uvw_LeadActivity    Customer.Id         199671760   Person      Id          1
  1703311661  uvw_LeadActivity    Customer.LastName   199671760   Person      Last        2
  1703311661  uvw_LeadActivity    Customer.FirstName  199671760   Person      First       3
  </code></pre>
  
  <p>Truth is I don't really need the whole result (query) just need to be able to get (using this example) the table and column for Customer.Email.  With that I can write the needed SQL to update.  I can't hard code this logic because the SQL DBA will over time update the view as need by the business.</p>
  
  <p>I hope this question is clear.  Thanks in advance!</p>
  
  <p>UPDATE: My hope is it be able to take in either a single column and updated value, or multiple column/values pairs into a stored procedure and update the based tables from that input.  Here is general outline...</p>
  
  <pre><code>create proc usp_UpdateData
      @EntityId int,
      @columnName varchar(max),
      @newVaule varchar(max)
  as
  
  Declare @baseTableName varchar(max)
  Declare @baseColumnName varchar(max)
  Declare @sqlCmd varchar(max)
  DECLARE @ParmDefinition varchar(max);
  
  Select @baseTableName=[Table], @baseColumnName=[Column] from [MAGICQUERY]
  
  SET @ParmDefinition = N'@Table varchar(max), @Column varchar(max), @Value varchar(max) @Id int'; 
  SET @sqlCmd = N'Update @Table Set @Column = @Value Where Id = @Id';
  
  EXECUTE sp_executesql @sqlCmd, @ParmDefinition, @Table = @baseTableName, @Column=@baseColumnName, @Value=@newVaule, @Id=@EntityId;
  </code></pre>
  
  <p>So the [MAGICQUERY] is what I am try to solve here.</p>