none
How to check if a view is updatable or not

    Question

  • What is a good way to find out if a view is updatable or not?

    The following options I know but are not useful for me:

    - try a update / insert statement: because I check all views existing in a unknown database

    - look for check_option: there are updatable views with check off

    - compare of create / modify date: modify date is also modified if an index is rebuild or if the view is changed even if it is not updateable  - not really sure about it, but I think so

    In sys.views I have not found a column with the information I ma looking for. I thought also about OBJECTPROPERTY, but I also have found nothing. Perhaps I have only not seen it ...

    Wednesday, July 24, 2013 11:46 AM

Answers

  • What is a good way to find out if a view is updatable or not?

    The following options I know but are not useful for me:

    - try a update / insert statement: because I check all views existing in a unknown database

    - look for check_option: there are updatable views with check off

    - compare of create / modify date: modify date is also modified if an index is rebuild or if the view is changed even if it is not updateable  - not really sure about it, but I think so

    In sys.views I have not found a column with the information I ma looking for. I thought also about OBJECTPROPERTY, but I also have found nothing. Perhaps I have only not seen it ...

    I think this is what you need..

    information_schema.views has an is_updatable column

    select * from information_schema.views

    Wednesday, July 24, 2013 2:15 PM
  • Oh thanks, yes, that's what I am looking for. I wonder me why I haven't seen it.

    But the comment microsoft gives, make me not really happy:

    IS_UPDATABLE


    varchar( 2)

    IS_UPDATABLE

    varchar(2)

    Specifies whether the view is updatable. Always returns NO.

    see: http://msdn.microsoft.com/en-us/library/ms181381%28v=sql.110%29.aspx

    The others have found some cases, when the view is not updateable. This means to me, that there exists updateable views for sql server or doesn't? I'm really confused about that.

    • Marked as answer by Sql Seeker Thursday, July 25, 2013 2:05 PM
    Wednesday, July 24, 2013 3:57 PM

All replies

  • You can modify the data of an underlying base table through a view, as long as the following conditions are true:

    • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

    • The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:

      • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

      • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

    • The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

    • TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause

    • Based on the above condition you can create a stored proc or function to check a view is updatable or not 


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Prajesh Wednesday, July 24, 2013 12:55 PM
    Wednesday, July 24, 2013 12:27 PM
  • I don't think there is an way to determine this. There are too many conditions.

    I suppose you could write a script that checks all of those conditions against the view. I have yet to come across it though.

    Wednesday, July 24, 2013 12:51 PM
  • Thanks.

    Other databases have a column in a table like sys.views which is named like is_updatable or have stored procedure to ask about. So I thought there must be something similar too.

    Wednesday, July 24, 2013 2:03 PM
  • What is a good way to find out if a view is updatable or not?

    The following options I know but are not useful for me:

    - try a update / insert statement: because I check all views existing in a unknown database

    - look for check_option: there are updatable views with check off

    - compare of create / modify date: modify date is also modified if an index is rebuild or if the view is changed even if it is not updateable  - not really sure about it, but I think so

    In sys.views I have not found a column with the information I ma looking for. I thought also about OBJECTPROPERTY, but I also have found nothing. Perhaps I have only not seen it ...

    I think this is what you need..

    information_schema.views has an is_updatable column

    select * from information_schema.views

    Wednesday, July 24, 2013 2:15 PM
  • Oh thanks, yes, that's what I am looking for. I wonder me why I haven't seen it.

    But the comment microsoft gives, make me not really happy:

    IS_UPDATABLE


    varchar( 2)

    IS_UPDATABLE

    varchar(2)

    Specifies whether the view is updatable. Always returns NO.

    see: http://msdn.microsoft.com/en-us/library/ms181381%28v=sql.110%29.aspx

    The others have found some cases, when the view is not updateable. This means to me, that there exists updateable views for sql server or doesn't? I'm really confused about that.

    • Marked as answer by Sql Seeker Thursday, July 25, 2013 2:05 PM
    Wednesday, July 24, 2013 3:57 PM
  • I'm curious why this is an issue.  Are you trying to hide the physical table layout for some users, and you don't want to publish some views now and only find out too late that you also need to update them and can't?

    Actually I'm surprised at how many views CAN be updated.  One limitation is you can only update one base table at a time, and that's true even of views that "are updateable".  And there was some other limitation on update statements that use a "from" clause, that I can't seem to find the details on right now.  I got into that a couple of years ago - because we *were* trying to hide physical table layout, etc!  Had to give it up, actually.

    Josh

    Wednesday, July 24, 2013 5:11 PM
  • Hi Sql Seeker

    If you want to allow users to update the views if they are updatable ? Is that your requirement  or just for POC purpose,

    (a) Its not a good practise to allows users to update views directly when you dont want to show them underline tables, the best approach would be to create stored procedures that allow them to update views/underline table and expose the signature of stored procedure only.

    (b) if this is POC purpose create a stored proc or function to check a view is updatable or not based on parametes specified by me above

    (c) Regarding Information Schema Views Some changes have been made to the information schema views that break backward compatibility. 

    http://msdn.microsoft.com/en-us/library/ms186778.aspx


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 9:43 AM
  • Hi,

    the only thing I want to do is to know if the view is updatable or not. Nothing more ...

    Microsoft SQL Server is the only system I know there is no simple way to do. I really wonder me why.

    I am not sure that I should implement some reasons for my own. There is still a hope: SQL Server 2014.

    2012 has got sequences, a great feature.


    Thursday, July 25, 2013 2:04 PM