Dynamic SQL, sp_RefreshView, and Profiler

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL, sp_RefreshView, and Profiler

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-03 : 08:26:25

Steve writes "I have a proc that I runs after large releases to refresh the views in the database.

Declare @name varchar(200)

declare c_views cursor for
select name
from sysobjects
where xtype = 'v'

open c_views

fetch next from c_views
into @name

while @@fetch_status = 0
begin

exec sp_refreshview @name

fetch next from c_views
into @name

end

close c_views
deallocate c_views

I wanted to gain some flexibility so I re-wrote it to eliminate the cursor and receive a database name as input.

Declare @DBName as varchar(100)

Select @DBName = 'ASETEST' --to be a input param

declare @maxrow int, @ii int
declare @sql nvarchar(500)
Declare @tablevar table (TID int IDENTITY, sql varchar(500))

insert @tablevar
SELECT @DBName+'.dbo.sp_refreshview ' + Name
FROM ASETEST.dbo.sysObjects where xType = 'v'
select @maxrow=@@rowcount,@ii =1

While @ii<= @maxrow
begin
select @sql = sql from @tablevar where tid=@ii
print @sql
exec (@sql)
set @ii = @ii +1
end

I ran profiler to verify that all of the views were being refreshed. I only saw one stmt executed (the first one generate).

Why am I not seeing all of the statements in Profiler?

Is the table var a good methodology for removing the cursor in this case?

Thanks,

Steve"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-03 : 12:30:24

What events do you have in your trace?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources