September 17, 2008 at 7:11 pm
Guys,
I have 550 views in the database, I am trying to find out views which are invalid or which give compilation errors.
Is there any system objects that can give this information or list of such views.
Any inputs would help.
Thanks
September 17, 2008 at 8:06 pm
I'm not aware of any instant way to get this list. But I would get started with this:
[font="Courier New"] use mydatabase
select * from information_schema.views[/font]
The TABLE_NAME field will give you the view name.
The VIEW_DEFINITION field will give you the T-SQL used in the view.
I woud then write a sp to parse out the T-SQL from the VIEW_DEFINITION field, add a "TOP 1" and a field with the TABLE_NAME value in it to the select statement, and then execute the T-SQL.
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
September 17, 2008 at 11:01 pm
Hi
bkdba suggession will help you.
Rajesh Kasturi
September 18, 2008 at 1:47 pm
Thanks Wandrag that helps
September 18, 2008 at 3:03 pm
Actually it's much easier than that and you don't need any evil Cursors either:
Declare @sql as NVarchar(MAX)
Set @sql = ''
Select @sql = @sql + 'EXEC sp_refreshview ''['
+ TABLE_SCHEMA + '].[' + TABLE_NAME + ']'';
' From INFORMATION_SCHEMA.VIEWS
Exec (@sql)
Any error messages will tell you which views have what problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2008 at 10:40 pm
rbarryyoung (9/18/2008)
Actually it's much easier than that and you don't need any evil Cursors either:
Declare @sql as NVarchar(MAX)
Set @sql = ''
Select @sql = @sql + 'EXEC sp_refreshview ''['
+ TABLE_SCHEMA + '].[' + TABLE_NAME + ']'';
' From INFORMATION_SCHEMA.VIEWS
Exec (@sql)
Any error messages will tell you which views have what problems.
Don't you just love the fact that you can overload variables in SQL Server like that? That doesn't work in most other very well known RDBMS's.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 19, 2008 at 1:12 am
in the way proposed by rbarryyoung, I have the message only for the first view that gives an error.
I developed this (modifying some code found on the net).
It uses a cursor, the TRY... CATCH construct but not dynamic SQL.
DECLARE @view sysname
DECLARE cviews CURSOR FOR
SELECT DISTINCT schema_name(schema_id) + '.' + name
FROM sys.objects so
WHERE type = 'V'
OPEN cViews
FETCH cViews INTO @view
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC sp_refreshview @view
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), @view
END CATCH
FETCH cViews INTO @view
END
DEALLOCATE cViews
It gives you a result for any view not refreshed correctly:
Invalid object name 'TBStat.dbo.tbl_Attivita'.dbo.vw_Attivita
See The Jenga blog
September 19, 2008 at 12:06 pm
Igor Brusetti (9/19/2008)
in the way proposed by rbarryyoung, I have the message only for the first view that gives an error.
Good point, but it is easy to fix this with TRY..CATCH also:
Declare @sql as NVarchar(MAX)
Set @sql = ''
Select @sql = @sql + 'BEGIN TRY
EXEC sp_refreshview ''['
+ TABLE_SCHEMA + '].[' + TABLE_NAME + ']'';
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ''['
+ TABLE_SCHEMA + '].[' + TABLE_NAME + ']'';
END CATCH
'
From INFORMATION_SCHEMA.VIEWS
Exec (@sql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 20, 2008 at 7:57 am
Jeff Moden (9/18/2008)
Don't you just love the fact that you can overload variables in SQL Server like that? That doesn't work in most other very well known RDBMS's.
I agree Jeff, and I wish MS would be a little more official in their support of this (especially the UPDATE version).
I am concerned though, that the term "variable overloading" could be confusing to folks who know object-oriented programming where it means something completely different. Some people (myself included) already call this technique "pseudocursors", what if we called the variables "accumulator variables"?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 20, 2008 at 8:43 am
Wandrag (9/18/2008)
Whoa -this is cool....:)Can you please explain how this works (Not the sp_refreshview part), but the fact that one don't have to loop through the view names to build the @sql string.
Jeff mentioned variable overloading - is that what's happing here.
(must i rather start a new thread?)
This is what some, including myself, call using an accumulator variable to create a pseudocursor. I have a short article on my blog (http://www.movingsql.com/) where I categorize this type (using SELECT) as a "simple" pseudocursor. (note: I am planning a more detailed article on Cursor taxonomy, pathology and treatment soon)
This technique relies first on the fact that although the semantics of good SQL are set-based, at some level its implementation must be row-by-row. And secondly on the obscure fact that although the SQL standards require that set-based data must clearly distinguish between its before and after set-states with no visible intermediate states (which prevents using table columns in an UPDATE statement like this), SQL Server places no such restrictions on scalar variables and in fact allows you to use them in SELECT and UPDATE statements as "accumulators".
So what happens here is that SQL Server seems to recognize that you are using a scalar variable to accumulate information and makes sure that the scalar variables intermediate states(values) are maintained at each row and carried forward to the "next" row, (though the definition of "next row" may be problematic). In this example we are taking advantage fo the Varchar(MAX) feature of SQL 2005 to accumulate a really big string of SQL commands that execute sp_refreshview for every view in the database, and then we execute it as dynamic SQL at the end.
The nice thing about pseudocursors is that they are fast and lightwieght because they hook into the row-by-row engine at a very low level and very unobtrusively to the data engine. As opposed to explicit Cursors which are slow and bloated and are extremely obtrusive to the data engine.
The problems with pseudocursors are:
1) Order can be a problem (usually solvable with Order By in SELECT's), but we didn't care about order here so that didn't matter. Note that almost any Cursor that does not really care about order can be easily converted to a simple pseudocursor like this one.
2) Simple pseudocursors (SELECT based ones) are limited in the number of problems that they can be applied to. For instancing, anything with subtotals are probably unworkable in simple pseudocursors.
3) Complex pseudocursors (UPDATE based ones) are much more powerful, but also have many more catches, particularly with correct ordering, and they are also much deeper in the swamp of "not supported but seems to (almost) always work".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 20, 2008 at 8:44 am
Wandrag (9/18/2008)
Whoa -this is cool....:)Can you please explain how this works (Not the sp_refreshview part), but the fact that one don't have to loop through the view names to build the @sql string.
Jeff mentioned variable overloading - is that what's happing here.
(must i rather start a new thread?)
"Overloading" is what I call it... dunno what others call it. If I remember correctly (been a long time since I've touched something like Java), the term "overloading" means something slightly different in the GUI world and pertains to objects.
Here, it means the variable is used over and over for each row in a table. Behind the scenes, SQL Server "loops through" a table or covered index to do a straight forward Select... heh, it's gotta "fetch" the data somehow! 😛 Each "row read" is like a micro transaction... it reads the whole row and it actually does it one at a time. If you use that idea of "loops behind the scene" with variables, each variable will have the value set by each row. If you concatenate a variable onto itself along with something from the table, you end up with the concatenated value of everything in the Select for that column. Think of it as a "set based cursor or while loop" without the additional overhead of a cursor or while loop. And, it's very, very fast.
A classic example of this type of simple "overlaying" of variables is, in fact, the concatenation example. Here's some "how to" examples and some things to avoid...
http://www.sqlservercentral.com/articles/Test+Data/61572/
If you want to see an example of overlaying variables on steroids, ya gotta see how to solve running totals using a similar method coupled with SQL Servers proprietary UPDATE SET @variable = column = expression.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
You can also use this same set based effect in reverse (parsing)... and this article probably explains how this "set based looping" technique works better than anything else. It also shows how to make a pot wad of dates in a range and a couple of other things but the key is how "set based loops" work.
http://www.sqlservercentral.com/articles/TSQL/62867/
Add that bit of knowledge to the fact that you can "overlay" the contents of variables using a single Select, and you can come up with some pretty fantastic stuff that most people think can only be done using an explicit loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2008 at 9:41 am
Heh... Barry's post wasn't there when I started typing my response.
I agree, Barry... I used to call variables such as these "accumulators" which is also probably more appropriate.
"Pseudo Cursors"? That's certainly a correct description, but the word (yeeeaach...) "cursor" (haaaacckkkk! Paaattoooiii!) sticks in my craw... I'll always refer to these as "Set Based Loops" just so I can avoid the "C" word. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2008 at 11:20 am
Jeff Moden (9/20/2008)
I agree, Barry... I used to call variables such as these "accumulators" which is also probably more appropriate."Pseudo Cursors"? That's certainly a correct description, but the word (yeeeaach...) "cursor" (haaaacckkkk! Paaattoooiii!) sticks in my craw... I'll always refer to these as "Set Based Loops" just so I can avoid the "C" word. 😛
Yeah, I know, but I came to terms with it by just not capitalizing it. I talk about this in one of my blog notes(On "cursors" and "Cursors"):
When discussions about Cursors get hot and heavy, one thing that comes out is that there is a lot of confusion about what a cursor (or "Cursor") really is. In general a cursor is "a moving placement or pointer that indicates a position."1 More specifically for databases, a cursor can be formally defined as "a control structure for the successive traversal (and potential processing) of records in a result set."2 Less formally, a cursor is any method (structures and processes) that serializes a data set, that is, sequences (imposes an order on) it and then processes it one record at a time, in order, keeping track of the current position within the sequence.
Given that definition, cursors are in a lot more places than we might initially assume and these more general cursors are not what we usually mean when we are talking about the Evils of Cursors. For that reason, I distinguish between general "cursors" (lower case) and the explicit user written SQL code constructs called "Cursors" (capitalized)...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 20, 2008 at 11:46 am
That's why, if you look closely, you'll notice that I always capitalize "Cursor", unless I am specifically talking about those other benign (and usually hidden) cursors. For instance, these include:
1) All data sets returned from SQL Server to the client, must go through a cursor. There's no other way that a client can receive and process it.
2) Likewise, all SELECT statements that return a dataset use a cursor at the very end in order to display the result set (note that this does not apply to SELECT expressions such as subqueries, views, etc., only to executed SELECT statements).
3) All ORDER BY clauses, wherever they occur, are necessarily (though implicitly) cursors. Note also, that anytime we impose an order on a data-set and then process the results (which we do a lot in our various code challenges) we are not really engaging in pure set-based SQL (because sets have no order), but rather in a partially set-based type of processing that we could call list-processing or list-based SQL.
4) All MERGE JOINs, because they require their inputs to be sorted, are also hidden cursors. etc., etc., ...
However, I would not call any of these types of cursors necessarily bad, and in fact they are often functionally necessary.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 20, 2008 at 3:37 pm
Barry... nice blog! But why don't you write that stuff as articles on SSC? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply