T-SQL Get Table Column based on View Column

Question

I have a SQL View that joins a few different tables, and in some cases alters the returned column [EDIT, not names] values. 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….

Table: Lead

• Columns: Id, Date, CustId, SalesId

Table: Person

• Columns: Id, First, Last, email

Table: Address

• Columns: Id, Line1, Line2, City, State, Zip

Then a view something like this…

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 

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.

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...

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]'

My results look something like this (condensed):

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

I really want it too look something like this (condensed):

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

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.

I hope this question is clear. Thanks in advance!

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...

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;

So the [MAGICQUERY] is what I am try to solve here.

Answer 1

Let me propose a bit different direction than what you are following currently.

There is a way to have updates applied to a View column values be propagated to the actual base table. Such Views are called updatable. However, there are restrictions like only one table can be used in the update and the view columns must reference table data directly, i.e. no computed or aggregated columns are allowed.

See the whole list of restrictions here Updatable Views . And comment if this is a possible way for you to go.

If your view doesn't mean the updatable requirements then the doc proposes to use the INSTEAD OF triggers.

I personally don't think it's a good idea to search internal SQL Server reference tables just to get the column name in the view base table and later update it. If updatable views doesn't work for you maybe you can consider changing the app to work with the base table directly during EDIT/UPDATE operation.

---

UPDATE1: second try, now with the INSTEAD OF trigger:

When you try to update the view and reference columns from 2+ tables, here is what happens:

update uvw_LeadActivity set 
[Customer.Email] = 'andrew2@server.com', -- col from Person
Address1='Addr1 St.'                     -- col from Address
go

Msg 4405, Level 16, State 1, Line 1
View or function 'uvw_LeadActivity' is not updatable because the modification affects multiple base tables.

Now if we define the INSTEAD OF UPDATE trigger for our view like the following:

--drop trigger tru_uvwLeadActvity
--go

CREATE TRIGGER tru_uvwLeadActvity
ON uvw_LeadActivity
INSTEAD OF UPDATE
AS
BEGIN
  UPDATE Person
  SET
    Email = INSERTED.[Customer.Email],
    Last = INSERTED.[Customer.LastName],
    First = INSERTED.[Customer.FirstName]    
  FROM INSERTED
  WHERE INSERTED.CustomerId = Person.Id    
  ;

  UPDATE Person
  SET
    Email = INSERTED.[Sales.Email],
    Last = INSERTED.[Sales.LastName],
    First = INSERTED.[Sales.FirstName]    
  FROM INSERTED
  WHERE INSERTED.SalesId = Person.Id    
  ;

  UPDATE Address
  SET  
    Line1 = INSERTED.Address1,
    Line2 = INSERTED.Address2,
    City = INSERTED.City,
    State = INSERTED.State,
    Zip = INSERTED.Zip    
  FROM INSERTED
  WHERE INSERTED.AddressId = Address.Id    
  ;

  print 'Updating 2 tables from uvw_LeadActivity'
END
go

and then we call our inter-table update on view:

set nocount on
update uvw_LeadActivity set 
[Customer.Email] = 'andrew2@server.com', Address1='Addr1 St.'
go

We get our message:

Updating 2 tables from uvw_LeadActivity

Selecting from the view again shows our columns did get updated:

So, all you need is to ask you DBA guys when updating base tables to also update this INSTEAD OF UPDATE trigger and you are done!.

Isn't it a nice solution? I think it is much better then going to sys.views and frieds... Let me know if this helps. Also vote ups are welcome ;).

---

UPDATE: some examples of the updatable view (tested with SQL Server 2008 R2):

create table Address (Id int not null identity(1,1), Line1 varchar(30), Line2 varchar(30), City varchar(30), State varchar(10), Zip varchar(10))
insert into Address (Line1,Line2, City, State, Zip) values ('Addr1_1', 'Addr_1_2', 'Houston', 'TX', '77001')
insert into Address (Line1,Line2, City, State, Zip) values ('Addr2_1', 'Addr_2_2', 'Atlanta', 'GA', '30301')
go
create table Person(Id int not null identity(1,1), First varchar(30), Last varchar(30), email varchar(30), fk_address_id int)
insert into Person (First, Last, email, fk_address_id) values ('Andrew', 'Customer', 'customer@server.com', 1)
insert into Person (First, Last, email, fk_address_id) values ('John', 'Sales', 'sales@server.com', 2)
go
create table Lead (Id int not null identity(1,1), Date datetime, CustId int, SalesId int)
insert into Lead (Date, CustId, SalesId) values (GETDATE(), 1,2)
go

Create view uvw_LeadActivity --WITH SCHEMABINDING
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
    dbo.Lead L
    Inner join dbo.Person C on C.Id = L.CustId
    Inner join dbo.Person S on S.Id = L.SalesId
    Inner join dbo.Address A on C.fk_address_id = A.id
go

select * from uvw_LeadActivity

update uvw_LeadActivity set [Customer.Email] = 'andrew@server.com'
go
select * from Person
go

Produces the following result: (note first select result comes from you and the second comes from the base table AFTER we have updated the VIEW).

Now, if you rename the base table column and try to select from the view, here is what happens:

sp_RENAME 'Person.Last' , 'Last_Name', 'COLUMN'
go
select * from uvw_LeadActivity
go

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 207, Level 16, State 1, Procedure uvw_LeadActivity, Line 7
Invalid column name 'Last'.
Msg 207, Level 16, State 1, Procedure uvw_LeadActivity, Line 17
Invalid column name 'Last'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'uvw_LeadActivity' because of binding errors.

But if you had your view defined with SCHEMABINDING like

Create view uvw_LeadActivity WITH SCHEMABINDING
As
...

SQL Server would not let you rename the base table column:

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 444
Object 'Person.Last' cannot be renamed because the object participates in enforced dependencies.

which is a nice way to prevent accidental base column renames/drops.

I hope I have understood correctly what you were trying to do and hope this helps.