0

Is there any way to use SQL Server metadata to find columns in a view that are the result of calculations/expressions? The sys.columns catalog view has the Is_Computed field, but that's returning 0 for a few fields in one of my views that are the result of a CASE expression, so I'm guessing it only works for calculated columns in tables. Is there any sort of similar metadata that works for views?

EDIT: What I'm trying to achieve here is an audit for some instead of triggers. I have a number of views that I need to make updateable with triggers, and I want to ensure that every column in the view is accounted for in the trigger so no data is lost. The trouble is that my query is flagging fields that can't be updated because they're the result of a computation, and I was hoping to find a way to exclude these from my results.

Jeff Rosenberg
  • 3,522
  • 1
  • 18
  • 38
  • Not really, as a view is basically a compiled query. What are you trying to achieve? – twoleggedhorse Mar 15 '13 at 14:43
  • To some extent, every column *could* be considered to be computed. Or consider a column which is a renaming of a column from a base table. – Damien_The_Unbeliever Mar 15 '13 at 14:45
  • Fair points, both of you. I've edited my question with more of an explanation of what I'm trying to achieve. – Jeff Rosenberg Mar 15 '13 at 14:48
  • Any column definition containing either "=" or " as " is, de facto, a computed column. Searching for these strings will return some trivial (e simple rename) calculated columns, but it may reduce the search space sufficiently to be of use. – Pieter Geerkens Mar 15 '13 at 17:57
  • @PieterGeerkens You're right, and that would do the trick for me, but I'm not sure how to get the column definition for columns on a view. Any suggestion there? – Jeff Rosenberg Mar 15 '13 at 18:18
  • @JeffRosenberg: THis link should help: http://stackoverflow.com/questions/4765323/is-there-a-way-to-retrieve-the-view-definition-from-a-sql-server-using-plain-ado – Pieter Geerkens Mar 15 '13 at 18:34
  • "views that I need to make updateable with triggers" - in Codd's original formulation of what would make a "Relational Database", this would have been automatic (rule 6). It turns out there's no way to do this all programtically – Damien_The_Unbeliever Mar 16 '13 at 18:11

0 Answers0