Question about Views and sp_refreshviews

[Sue]

I wrote a script that uses the sp_refreshviews . The script will be part
of a larger one that is automatically run in multiple databases where
different views exist.

Question:
My understanding of views is that by simply stating 'select * from
myviewname where 0=1' that the view is recompiled. If so, what
advantages are there in using sp_refreshviews ?
I couldn't handle the errors that sp_refreshview produced (I am sure
due to my lack of sql knowledge.): however, I found the select
statement above would allow me to check for the views validity so I
used it to capture the errors instead. I have included my script here.
I would appreciate any advice or information that will improve my
understanding of sql, views, and/or internal stored procedure:
sp_refreshviews .

Regards,
Sue

-- Drop table #tmpViewName
SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
Declare @intTotal int
Declare @intcounter int
Declare @ViewsName varchar(255)
Declare @SQL varchar(8000)
Declare @DropViewCmd varchar(8000)
Declare @myError int

Set @intTotal = 0
Set @intcounter = 1

--First create new table to hold invalid views if it doesn't already
exist
IF not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[invalidViews]')
and OBJECTPROPERTY( id, N'IsUserTable') = 1) Begin

CREATE TABLE dbo.[invalidViews] (
[ViewID] [int] IDENTITY (1, 1) NOT NULL ,
[Viewname] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[ViewText] [varchar] (4000) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[DropViewDate] [DateTime] NOT NULL
) ON [PRIMARY]

End

SELECT IDENTITY(int,1, 1) as rowID, name as 'viewname' into
#tmpViewName
FROM sysobjects,sysc omments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'V'
AND sysobjects.cate gory=0
Select @intTotal = count(*) from #tmpViewName

While @intcounter <= @intTotal begin

Set @SQL = ''
Set @DropViewCmd = ''
Set @ViewsName = ''
Set @myError = 0

Select @ViewsName = viewname from #tmpViewName where rowID =
@intcounter

Set @SQL = 'Select * from ' + @ViewsName + ' where 0=1'
exec(@SQL)

Set @myError = @@Error

If @myError > 0 Begin

Insert into dbo.invalidView s
Select name as 'viewname', text as 'viewtext', GetDate()
FROM sysobjects,sysc omments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'V'
AND sysobjects.cate gory=0
AND sysobjects.name = @ViewsName

Set @DropViewCmd = 'DROP VIEW ' + @ViewsName
exec(@DropViewC md)
print 'Invalid View ' + @ViewsName + ' was dropped.'

End
Else Begin
exec sp_refreshview @ViewsName
print 'Valid View ' + @ViewsName + ' was refreshed.'
End

Set @intcounter = @intcounter + 1

End

GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS OFF

[Hugo Kornelis]

On 7 Nov 2005 16:53:51 -0800, Sue wrote:

Question:
My understanding of views is that by simply stating 'select * from
myviewname where 0=1' that the view is recompiled. If so, what
advantages are there in using sp_refreshviews ?

Hi Sue,

Views are never compiled, much less recompiled.

Views are more like macros then anything else: when you include them in
a query, their name is replaced by their definition before the query
optizer starts to think about the bests way to execute the query.

If you execute
select * from myviewname where 0=1
Then the optimizer will "see" this:
select * from (insert view-definition here) AS myviewname where 0=1"
The goal of sp_refreshview is not to recompile, but to update the
meta-data of a view. This is only necessary for views that were created
with SELECT * (which should be avoided anyway!), and only if the
underlying tables have been changed since the view was created or
refreshed.

Here's an example of where you would use sp_refreshview:

CREATE TABLE Test
(A char(1) NOT NULL,
C char(1) NOT NULL
)
go
CREATE VIEW GoodView
AS
SELECT A, C FROM Test
go
CREATE VIEW BadView
AS
SELECT * FROM Test
go
INSERT INTO Test (A, C)
SELECT 'A', 'C'
go
PRINT 'Before changing the table'
SELECT * FROM GoodView
SELECT * FROM BadView
go
DROP TABLE Test
go
CREATE TABLE Test
(A char(1) NOT NULL,
B char(1) NOT NULL,
C char(1) NOT NULL
)
go
INSERT INTO Test (A, B, C)
SELECT 'A', 'B', 'C'
go
PRINT 'After changing the table'
SELECT * FROM GoodView
SELECT * FROM BadView
go
exec sp_refreshview GoodView
exec sp_refreshview BadView
go
PRINT 'After refreshing the views'
SELECT * FROM GoodView
SELECT * FROM BadView
go
DROP VIEW BadView
DROP VIEW GoodView
go
DROP TABLE Test
go
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Susie-Q]

Hugo,

Thanks for replying to my post. I now understand that the sp_refreshview
updates the meta-data for the view. I assume the meta-data is in one of
the system tables.

However, I would like to know if my script could be written differently.
The sp_refreshviews raises a 'fatal' error when it cannot recreate a
view. I had originally tried to capture the error code using @@error but
as soon as the 'severe' error was encountered it kicked me out of the
loop. I found that I could validate the view with the above select
statement. It also fails when it encounters an error message but allows
me to capture it and continue to loop. Do you have any suggestions or
insight into a better way to handle the error issue? I need to loop
through the views in each database. I never know what views are or are
not in them.

Regards,
Susie

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Susie-Q (an*******@devd ex.com) writes:

However, I would like to know if my script could be written differently.
The sp_refreshviews raises a 'fatal' error when it cannot recreate a
view. I had originally tried to capture the error code using @@error but
as soon as the 'severe' error was encountered it kicked me out of the
loop. I found that I could validate the view with the above select
statement. It also fails when it encounters an error message but allows
me to capture it and continue to loop. Do you have any suggestions or
insight into a better way to handle the error issue? I need to loop
through the views in each database. I never know what views are or are
not in them.

In SQL Server 2000, errors can have different effects. Some terminate the
current statement, some the current scope and some the current batch.
And there is no way you can catch this. I would suggest that you implement
the the loop in some client language, VBscript, Perl or whatever your
prefer.

In SQL 2005, there is vastly improved error handling, and there you can
catch all errors.

Of course, the best workaround is to get all those SELECT * in the views
out of the house. Then you don't to bother about sp_refreshview.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Guzman]

> Of course, the best workaround is to get all those SELECT * in the views

out of the house. Then you don't to bother about sp_refreshview.
I agree that SELECT * should be avoided but I wouldn't go as far as to say
that sp_refreshview isn't needed when an explicit column list is
specified. The view meta data may still get out-of-sync. This probably
isn't a show-stopper but this looks to be like an accident waiting to
happen.

CREATE TABLE Table1(Col1 int)
GO
CREATE VIEW View1 AS SELECT Col1 FROM Table1
GO
EXEC sp_help 'View1'
GO
ALTER TABLE Table1
ALTER COLUMN Col1 varchar(20)
GO
EXEC sp_help 'View1'
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1... Susie-Q (an*******@devd ex.com) writes:

However, I would like to know if my script could be written differently.
The sp_refreshviews raises a 'fatal' error when it cannot recreate a
view. I had originally tried to capture the error code using @@error but
as soon as the 'severe' error was encountered it kicked me out of the
loop. I found that I could validate the view with the above select
statement. It also fails when it encounters an error message but allows
me to capture it and continue to loop. Do you have any suggestions or
insight into a better way to handle the error issue? I need to loop
through the views in each database. I never know what views are or are
not in them.

In SQL Server 2000, errors can have different effects. Some terminate the
current statement, some the current scope and some the current batch.
And there is no way you can catch this. I would suggest that you implement
the the loop in some client language, VBscript, Perl or whatever your
prefer.

In SQL 2005, there is vastly improved error handling, and there you can
catch all errors.

Of course, the best workaround is to get all those SELECT * in the views
out of the house. Then you don't to bother about sp_refreshview.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Dan Guzman (gu******@nospa m-online.sbcgloba l.net) writes:

I agree that SELECT * should be avoided but I wouldn't go as far as to say
that sp_refreshview isn't needed when an explicit column list is
specified. The view meta data may still get out-of-sync. This probably
isn't a show-stopper but this looks to be like an accident waiting to
happen.

OK, I change my statement to say that views should not be used. :-) That
would prevent me from saying silly things on the newsgroups.

Thanks for the correction, Dan.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp