473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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_IDENTIFIER 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_General_CP1_CI_AS NOT
NULL ,
[ViewText] [varchar] (4000) COLLATE SQL_Latin1_General_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,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'V'
AND sysobjects.category=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.invalidViews
Select name as 'viewname', text as 'viewtext', GetDate()
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'V'
AND sysobjects.category=0
AND sysobjects.name = @ViewsName

Set @DropViewCmd = 'DROP VIEW ' + @ViewsName
exec(@DropViewCmd)
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_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF

Nov 8 '05 #1
5 4425
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)
Nov 8 '05 #2
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 ***
Nov 8 '05 #3
Susie-Q (an*******@devdex.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****@sommarskog.se

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

Nov 8 '05 #4
> 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****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1... Susie-Q (an*******@devdex.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****@sommarskog.se

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

Nov 9 '05 #5
Dan Guzman (gu******@nospam-online.sbcglobal.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****@sommarskog.se

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

Nov 9 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: YoBro | last post by:
Hi, I have a database that stores in a field the number of views for a product. I want to write a query that gets the two highest views, so I can then output the result. I have this so far,...
3
by: Dave Sisk | last post by:
Hi Folks: I'm a little new to SQLServer, so please pardon my ignorance! I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and TABLE_CONSTRAINTS. I'm looking for the views that will...
5
by: Matt | last post by:
I am working in a project where the business model is complex enough that many common retrieval functions become difficult to develop and maintain in single query statements or functions. I have...
4
by: Lauren Quantrell | last post by:
In my old MDB databases, I constructed a lot of "subQueries" to filter out records, then based a new query on the subQuery. This results in huge speed increases on large datasets. However... In...
9
by: Riley DeWiley | last post by:
I have a programming problem in OLEDB and C++ that seems to be pointing me toward using layered views and hierarchical rowsets. However, I am uncertain of the precise implementation and need...
2
by: dbuchanan52 | last post by:
Hello, I am building an application for Windows Forms using. I am new to SQL Server 'Views'. Are the following correct understanding of their use? 1.) I believe a view can be referenced in a...
15
by: rod.weir | last post by:
Fellow database developers, I would like to draw on your experience with views. I have a database that includes many views. Sometimes, views contains other views, and those views in turn may...
5
by: -pb- | last post by:
Hi, We are developing an windows application and decided to use the MVC design pattern. We decided to use windows application due to varuous business processes which cannot be implemented in web...
7
by: Gary | last post by:
Hello guys! Bear with me, I am a newbie. She is the Data Warehouse manager. She has about 50 users to use the Oracle database from M$ Access via ODBC connection. All those users have only...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.