Force SQL Server to recompile stored procedures every time they run (SQL Server 7/2000)

This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled stored
procedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.
Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the nature
of a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highly
specialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by the
savings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of your
application to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this:
CREATE PROCEDURE ProcName
@.Param int /* ... other parameters */
WITH RECOMPILE
AS /* ... procedure code follows */

If we omit "WITH RECOMPILE", what will be the consequence? Thanks

WITH RECOMPILE can kill an Asp.net application because HTTP is stateless. The better solution is to force SQL Server to put all your stored procs in the procedure cache on start up. There is a stored proc in the Master called SP(system stored proc) Procoption you can use it to auto start all your stored procs. Recompile is modified in SQL Server 2005 you can recompile only the line you need then your solution will be ok for now there are alternatives. See code below the only value for option is Startup and value is true for ON and false for OFF. Hope this helps.

sp_procoption[@.ProcName =]'procedure'
,[@.OptionName =]'option'
,[@.OptionValue =]'value'

|||

Please kindly elaborate more on: "WITH RECOMPILE can kill an Asp.net application because HTTP is stateless." What do "kill" and "stateless" mean here? Thanks again.

|||

You get the best performance if all your stored procs are in the procedure cache all the time WITH RECOMPILE will not allow that, so everytime your stored proc is accessed your user will wait for SQL Server to recompile the stored proc before executing it. In Asp.net some processes will time out before your stored proc will execute. Kill means your code will timeout and stateless means a protocol without state HTTP is one of them. Your users will wait for SQL Server which is session to finish before objects on your pages can be accessed. The first thing to know about stored procs is avoid Recompile even in Windows appilcation. Hope this helps.

Force Recompile on all Views and Stored Procs

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:
>
|||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:
[vbcol=seagreen]
> Is there a way to script a loop of all views and Stored Procs instead of
> creating a static list?
> "Yih-Yoon Lee" 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.
|||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...
> to
>
>
|||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:
[vbcol=seagreen]
> 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:
|||> 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:
[vbcol=seagreen]
> 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:

Force Recompile on all Views and Stored Procs

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:
>|||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 usin
g
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:
[vbcol=seagreen]
> Is there a way to script a loop of all views and Stored Procs instead of
> creating a static list?
> "Yih-Yoon Lee" 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.|||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...
> to
>
>|||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:
[vbcol=seagreen]
> Here are some examples, using undocumented stored procedure sp_execresults
et
> (do not recommend using it in production) and using a cursor to traverse
> procedures and views and recompile using sp_recompile and refresh views us
ing
> 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:
>|||> 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:
[vbcol=seagreen]
> This looks good, but I need something I can run after updates in Productio
n.
> Why don't you recommend using this in production?
> "Alejandro Mesa" wrote:
>

Force Recompile on all Views and Stored Procs

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.
> > > >