Posted by Sue on 12/05/31 11:31

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

[Back to original message]