Handy Utility Refreshes Views - 17 Dec 1999

The discussion of views in this column has become very interesting. Readers have contributed questions and suggestions on creating views with SQL Server. Mike Chinni contributed the following stored procedure for SQL Server 7.0 that you can use to refresh the views in a database. This utility is handy because it's fast and you can execute it remotely from any place you can execute a stored procedure from. This method makes updating views on your system lightning fast. Here's the code:

CREATE Procedure procRefreshViews as
declare #curRefreshViews scroll cursor 
  for select name from sysobjects where xtype = 'v' 
  and status >= 0

declare @viewName sysname, @cmd varchar(1000)
open #curRefreshViews
fetch next from #curRefreshViews into @viewName
while (@@fetch_status  -1)
begin
  set @cmd = 'sp_refreshview ' + @viewName
  execute(@cmd)
  fetch next from #curRefreshViews into @viewName
end
close #curRefreshViews
deallocate #curRefreshViews

After I read Mike's note, which included this procedure, I arrived at a client's site. The client and I started exploring views, stored procedures, and triggers with SQL Server 7.0. You can use the Visual Database tools in SQL Server Enterprise Manager and Visual Studio to create and manage views, stored procedures, and triggers. Because we were using Visual InterDev when we began the discussion, we looked at what Visual InterDev does with the previous stored procedure. You access the Visual Database features in Visual InterDev from the Data View window. You can find the views in your database in the Views folder and the stored procedures in the Stored Procedures folder. The triggers will appear at the end of the columns in a table. You can double-click the name of a view, stored procedure, or trigger to open it in the editor. The text editor is the same one you use for Active Server Pages (ASP) script, but this editor also color-codes Transact SQL (T-SQL). You can also use the SQL Server Query Designer to edit SQL code for a procedure. I usually use the clipboard to copy and paste SQL code from the editor to the Query Designer.

Be aware that the way you implement the Visual Database tools in Visual InterDev is different from the way you implement them in Visual Basic (VB). Even so, the tools’ internal workings are the same, and that makes them easy to use.