Is there a command or a script that will force all views and Stored Procs to
recompile? I'm trying to resolve the issue when views fails because field
order is changed in a Database structure.
I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
but it doesn't recompile until the next call of the stored Proc.Don't think that there is a database or server wide command to do that.
You have to do it in the SP or view level.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
jmhmaine wrote:
> Is there a command or a script that will force all views and Stored Procs to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.|||Is there a way to script a loop of all views and Stored Procs instead of
creating a static list?
"Yih-Yoon Lee" wrote:
> Don't think that there is a database or server wide command to do that.
> You have to do it in the SP or view level.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> jmhmaine wrote:
> > Is there a command or a script that will force all views and Stored Procs to
> > recompile? I'm trying to resolve the issue when views fails because field
> > order is changed in a Database structure.
> >
> > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > but it doesn't recompile until the next call of the stored Proc.
>|||here's something that may be helpful - it will return a resultset that
you can use, i.e., not actually run any drops.
be sure to review the output before you run the output though.
-- creates a script that drops all stored procedures and views.
-- excludes procedures starting w/ dt_ and sys.
begin
declare @.procName sysname
declare @.procType char(2)
declare @.dropProcSql varchar(256)
create table #procNameTbl (procName sysname)
declare procCursor cursor for
select name, type from sysobjects
where type in ('P', 'V') and
substring(name, 1, 3) <> 'dt_' and
substring(name, 1, 3) <> 'sys'
order by name
open procCursor
fetch next from procCursor into @.procName, @.procType
while @.@.fetch_status = 0
begin
if @.procType = 'P'
set @.dropProcSql = 'drop procedure ' + @.procName
else
set @.dropProcSql = 'drop view ' + @.procName
insert into #procNameTbl values (@.dropProcSql)
fetch next from procCursor into @.procName, @.procType
end
close procCursor
deallocate procCursor
select * from #procNameTbl
drop table #procNameTbl
end
go|||Here are some examples, using undocumented stored procedure sp_execresultset
(do not recommend using it in production) and using a cursor to traverse
procedures and views and recompile using sp_recompile and refresh views using
sp_refreshview.
Example:
use northwind
go
execute sp_execresultset N'
select
''execute sp_recompile '' + quotename(routine_name)
from
information_schema.routines
where
routine_type = ''procedure''
and objectproperty(object_id(routine_schema + ''.'' +
quotename(routine_name)), ''IsMSShipped'') = 0'
go
declare @.rn sysname
declare @.sql nvarchar(4000)
declare routines_cursor cursor local fast_forward
for
select
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(routine_schema + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open routines_cursor
while 1 = 1
begin
fetch next from routines_cursor into @.rn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_recompile ' + quotename(@.rn)
execute sp_executesql @.sql
end
close routines_cursor
deallocate routines_cursor
go
execute sp_execresultset N'
select
''execute sp_refreshview '' + quotename(table_name)
from
information_schema.views
where
objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
''IsMSShipped'') = 0'
go
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_name
from
information_schema.views
where
objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.tn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
execute sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
go
AMB
"jmhmaine" wrote:
> Is there a way to script a loop of all views and Stored Procs instead of
> creating a static list?
> "Yih-Yoon Lee" wrote:
> > Don't think that there is a database or server wide command to do that.
> > You have to do it in the SP or view level.
> >
> > Yih-Yoon Lee
> > My blog http://www.mssql-tools.com/blog
> > E-mail: yihyoon.online@.gmail.com
> > /* remove .online to send me e-mail */
> >
> > jmhmaine wrote:
> > > Is there a command or a script that will force all views and Stored Procs to
> > > recompile? I'm trying to resolve the issue when views fails because field
> > > order is changed in a Database structure.
> > >
> > > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > > but it doesn't recompile until the next call of the stored Proc.
> >|||I would use here DBCC FREEPROCCACHE to remove every cached execution plan
from memory. You must have administrative rights however to execute this.
Marc
"jmhmaine" <jmh@.online.nospam> wrote in message
news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> Is there a command or a script that will force all views and Stored Procs
to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.|||I believe this is only for Stored Procs, not views.
"Marc Mertens" wrote:
> I would use here DBCC FREEPROCCACHE to remove every cached execution plan
> from memory. You must have administrative rights however to execute this.
> Marc
> "jmhmaine" <jmh@.online.nospam> wrote in message
> news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> > Is there a command or a script that will force all views and Stored Procs
> to
> > recompile? I'm trying to resolve the issue when views fails because field
> > order is changed in a Database structure.
> >
> > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > but it doesn't recompile until the next call of the stored Proc.
>
>|||This looks good, but I need something I can run after updates in Production.
Why don't you recommend using this in production?
"Alejandro Mesa" wrote:
> Here are some examples, using undocumented stored procedure sp_execresultset
> (do not recommend using it in production) and using a cursor to traverse
> procedures and views and recompile using sp_recompile and refresh views using
> sp_refreshview.
> Example:
> use northwind
> go
> execute sp_execresultset N'
> select
> ''execute sp_recompile '' + quotename(routine_name)
> from
> information_schema.routines
> where
> routine_type = ''procedure''
> and objectproperty(object_id(routine_schema + ''.'' +
> quotename(routine_name)), ''IsMSShipped'') = 0'
> go
> declare @.rn sysname
> declare @.sql nvarchar(4000)
> declare routines_cursor cursor local fast_forward
> for
> select
> routine_name
> from
> information_schema.routines
> where
> routine_type = 'procedure'
> and objectproperty(object_id(routine_schema + '.' +
> quotename(routine_name)), 'IsMSShipped') = 0
> open routines_cursor
> while 1 = 1
> begin
> fetch next from routines_cursor into @.rn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_recompile ' + quotename(@.rn)
> execute sp_executesql @.sql
> end
> close routines_cursor
> deallocate routines_cursor
> go
> execute sp_execresultset N'
> select
> ''execute sp_refreshview '' + quotename(table_name)
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
> ''IsMSShipped'') = 0'
> go
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> declare views_cursor cursor local fast_forward
> for
> select
> table_name
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> 'IsMSShipped') = 0
> open views_cursor
> while 1 = 1
> begin
> fetch next from views_cursor into @.tn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
> execute sp_executesql @.sql
> end
> close views_cursor
> deallocate views_cursor
> go
>
> AMB
> "jmhmaine" wrote:
> > Is there a way to script a loop of all views and Stored Procs instead of
> > creating a static list?
> >
> > "Yih-Yoon Lee" wrote:
> >
> > > Don't think that there is a database or server wide command to do that.
> > > You have to do it in the SP or view level.
> > >
> > > Yih-Yoon Lee
> > > My blog http://www.mssql-tools.com/blog
> > > E-mail: yihyoon.online@.gmail.com
> > > /* remove .online to send me e-mail */
> > >
> > > jmhmaine wrote:
> > > > Is there a command or a script that will force all views and Stored Procs to
> > > > recompile? I'm trying to resolve the issue when views fails because field
> > > > order is changed in a Database structure.
> > > >
> > > > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > > > but it doesn't recompile until the next call of the stored Proc.
> > >|||> Why don't you recommend using this in production?
I posted an example using sp_execresultset (do not use this one in
production because this sp is not documented in BOL and microsoft can change
it without giving us a notice) and another using a cursor to traverse
routines and views (use these ones).
AMB
"jmhmaine" wrote:
> This looks good, but I need something I can run after updates in Production.
> Why don't you recommend using this in production?
> "Alejandro Mesa" wrote:
> > Here are some examples, using undocumented stored procedure sp_execresultset
> > (do not recommend using it in production) and using a cursor to traverse
> > procedures and views and recompile using sp_recompile and refresh views using
> > sp_refreshview.
> >
> > Example:
> >
> > use northwind
> > go
> >
> > execute sp_execresultset N'
> > select
> > ''execute sp_recompile '' + quotename(routine_name)
> > from
> > information_schema.routines
> > where
> > routine_type = ''procedure''
> > and objectproperty(object_id(routine_schema + ''.'' +
> > quotename(routine_name)), ''IsMSShipped'') = 0'
> > go
> >
> > declare @.rn sysname
> > declare @.sql nvarchar(4000)
> > declare routines_cursor cursor local fast_forward
> > for
> > select
> > routine_name
> > from
> > information_schema.routines
> > where
> > routine_type = 'procedure'
> > and objectproperty(object_id(routine_schema + '.' +
> > quotename(routine_name)), 'IsMSShipped') = 0
> >
> > open routines_cursor
> >
> > while 1 = 1
> > begin
> > fetch next from routines_cursor into @.rn
> >
> > if @.@.error <> 0 or @.@.fetch_status <> 0 break
> >
> > set @.sql = N'execute sp_recompile ' + quotename(@.rn)
> >
> > execute sp_executesql @.sql
> > end
> >
> > close routines_cursor
> > deallocate routines_cursor
> > go
> >
> > execute sp_execresultset N'
> > select
> > ''execute sp_refreshview '' + quotename(table_name)
> > from
> > information_schema.views
> > where
> > objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
> > ''IsMSShipped'') = 0'
> > go
> >
> > declare @.tn sysname
> > declare @.sql nvarchar(4000)
> > declare views_cursor cursor local fast_forward
> > for
> > select
> > table_name
> > from
> > information_schema.views
> > where
> > objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> > 'IsMSShipped') = 0
> >
> > open views_cursor
> >
> > while 1 = 1
> > begin
> > fetch next from views_cursor into @.tn
> >
> > if @.@.error <> 0 or @.@.fetch_status <> 0 break
> >
> > set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
> >
> > execute sp_executesql @.sql
> > end
> >
> > close views_cursor
> > deallocate views_cursor
> > go
> >
> >
> > AMB
> >
> > "jmhmaine" wrote:
> >
> > > Is there a way to script a loop of all views and Stored Procs instead of
> > > creating a static list?
> > >
> > > "Yih-Yoon Lee" wrote:
> > >
> > > > Don't think that there is a database or server wide command to do that.
> > > > You have to do it in the SP or view level.
> > > >
> > > > Yih-Yoon Lee
> > > > My blog http://www.mssql-tools.com/blog
> > > > E-mail: yihyoon.online@.gmail.com
> > > > /* remove .online to send me e-mail */
> > > >
> > > > jmhmaine wrote:
> > > > > Is there a command or a script that will force all views and Stored Procs to
> > > > > recompile? I'm trying to resolve the issue when views fails because field
> > > > > order is changed in a Database structure.
> > > > >
> > > > > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > > > > but it doesn't recompile until the next call of the stored Proc.
> > > >
