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

For Stored Procs Gurus

Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael Murphy
Please do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>

For Stored Procs Gurus

Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael MurphyPlease do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>