March 2, 2009 at 10:14 am
lets assume i have a view such as this
create view [myview]
as
SELECT
[col_1],
[col_2],
[col_1] + [col_2] as [cols_combined]
from [mytable]
now sql will say that the view in not updatable but it will actually lets this view be able to have insert and update quieries run against it as long as you do not reference the cols_combined field.
I'm attempting to track down and find these views and columns to document in our system (~5000 views in our DB).
i tried queries against the iscomputed columnproperty on the INFORMATION_SCHEMA.Columns thinking that might work but it always returns 0...
i've been digging around trying to find something to let me find these columns but i'm just stumped.
any help would be greatly appreciated!
thanks!
March 2, 2009 at 2:43 pm
I setup a test scenario similiar to what you had created. I first tried finding any objects intellisense picked up that might help (through 2008) with no luck. I then setup a trace and audit and ran the update statement in question to see if it would pick up any checks prior to the actual failure that might give some clues. No luck there. This query might give you a starting point. The best I figure, if you can get a list of the text of the views and can find some fancy regular expression work to filter out the ones with computed columns it might just work. Hopefully someone with better reg. exp. skills than myself can help chime in:
SELECT sc.texttype, sc.text, DATALENGTH(sc.text)
FROM (sys.schemas ss INNER JOIN sys.objects so ON
ss.schema_id = so.schema_id) INNER JOIN syscomments sc ON
so.object_id = sc.id
WHERE (so.type = N'V') --LIMIT THIS TO ONLY VIEWS
ORDER BY sc.colid;
Cheers,
Brian
March 3, 2009 at 8:31 am
I'm not sure this will grab everything, but give it a try. It basically looks for views that have an operation (*,/,-,+,%) followed by a FROM somewhere after it (to ensure it is in the select clause). Also there must be a alphanumeric or space before the operation to ensure it isn't a "select *". Let me know if this works.
USE [dbName];
SELECT so.name, sc.text, DATALENGTH(sc.text)
FROM (sys.schemas ss INNER JOIN sys.objects so ON
ss.schema_id = so.schema_id) INNER JOIN syscomments sc ON
so.object_id = sc.id
WHERE (so.type = N'V') --LIMIT THIS TO ONLY VIEWS
AND PATINDEX('%[a-zA-Z0-9|\ ][\*\/\%\+\-]%FROM%',sc.text) <> 0
ORDER BY sc.colid;
GO
Cheers,
Brian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply