Solved

Changes to tables and Stored Procedures

Posted on 2004-04-09
24
434 Views
Last Modified: 2008-03-03
I know if you make a change to a table you need to use sp_Refreshview on your views that use the table.  I have a stored Proc that does this for each view.

My question is do you need to do something similar to stored procs that use the table?

If you do, is there a stored proc that would go through each of your procedures and recompile or what ever you need to do to each procedure?

Thanks in advance for any help.

LJG
0
Comment
Question by:LJG
  • 10
  • 5
  • 5
  • +3
24 Comments
 
LVL 6

Expert Comment

by:billy21
ID: 10791331
AFAIK there is no stored proc that will recompile all stored procedures.  There is this one though to recompile a given procedure:

sp_Recompile MyProcedure

You can write your own stored procedure to make this apply to all procedures using a cursor and some dynamic sql.
0
 
LVL 6

Expert Comment

by:billy21
ID: 10791360
Recompiling stored procedures is only necessary when either indices, table structure or the sql inside the SP changes.  sp_recompile marks the procedure for recompilation.  The recompilation actually occurs the next time the procedure is executed.  When an SP is recompiled all it means is that the execution plan is regenerated.

Another alternative, if your table structure and indices are changing very regularly is to use the With Recompile hint on your create procedure statement ie.

Create Procedure MyProc With Recompile
AS
   Select * from something
Go
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10791374
select name
 from sysobjects where xtype = 'P'

will get you the names of all stored procedures

declare @procName as varchar(20)
declare c1 cursor for
select name
 from sysobjects where xtype = 'P'

open c1

fetch c1 into @procName

while(@@fetch_status = 0)
begin

   sp_Recompile @procName
end
close c1
deallocate c1
0
 
LVL 6

Expert Comment

by:billy21
ID: 10791375
Note that the with recompile hint causes the execution plan to be regenerated every time you execute the procedure.  In most situations it is not the right option.
0
 
LVL 6

Expert Comment

by:billy21
ID: 10791404
Right you are Jayrod.  You actually don't need dynamic sql at all.  I didn't realise sp_recompile could also accept the name of the procedure in varchar format.
0
 
LVL 6

Expert Comment

by:billy21
ID: 10791410
You should include UDFs and Triggers too.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10791419
But you do need EXEC before it, as in:

EXEC sp_recompile @procName

It's safest to *always* use EXEC, especially in stored code (procedures, triggers, etc.)
0
 
LVL 6

Expert Comment

by:billy21
ID: 10791435
Why is it safe to use exec?  I never use it.  Is it for backwards compatibility/

select name
 from sysobjects where xtype = 'P'

will get you the names of all stored procedures

declare @procName as varchar(20)
declare c1 cursor for
select name
 from sysobjects where xtype In('P','FN','TF','IF','TR')

open c1

fetch c1 into @procName

while(@@fetch_status = 0)
begin

   sp_Recompile @procName
end
close c1
deallocate c1
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10791473
What??  It's only optional at the start of a new batch, and even then, it's *not* a good idea.  Try doing a syntax check on the code above (first comment out the "will get you the names of all stored procedures") and you will see what I mean:

Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'sp_Recompile'.
0
 
LVL 6

Accepted Solution

by:
billy21 earned 125 total points
ID: 10791489
This one works.  VarChar(20) is too restrictive and causes errors.  I've added all types of sps including the 3 types functions and triggers and extended the size of the @procname variable and of course included exec.

declare @procName as varchar(200)
declare c1 cursor for
select name
from sysobjects where xtype In('P','FN','TF','IF','TR')
And Name Not Like 'dt_%'

open c1

fetch c1 into @procName

while(@@fetch_status = 0)
begin

      Exec sp_Recompile @procName
      fetch c1 into @procName
end
close c1
deallocate c1
0
 
LVL 6

Expert Comment

by:billy21
ID: 10791499
Yep Scott.  Right you are.  I didn't know that.
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10791520
sorry I rushed through the cursor above and wrote it in this text box off the top of my head as a starting point.

sheesh ya have to be fast around here with the likes of billy and scott around :P
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Expert Comment

by:billy21
ID: 10791525
hmmm... Couldn't hurt to throw in an sp_updatestats too as part of your maintenance script.
0
 
LVL 6

Expert Comment

by:billy21
ID: 10791543
If you follow a proper naming convention for your stored procedures (and don't prefix them with the sp_ which is the standard for system stored procedures) you could include this line in your query to exclude all system stored procedures:

And Name Not Like 'sp_%'
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10791649
jayrod:

yes, you do have to be fast sometimes :-) , but in this case you were fast enough; the core of the solution is your first post.
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10791673
well billy found the recompile sp.... There are sooo many of them to remember :)

also I just found that EXECUTE IMMEDIATE may be a better substitute then just execute.. not sure I'll have to test that
0
 
LVL 34

Expert Comment

by:arbert
ID: 10791792
billy21's solution is close.  I would just change the query not to use the system tables and use the information_schema view like this:


declare @procName as varchar(200)
declare c1 cursor for
select distinct routine_name  from information_schema.routines

open c1

fetch c1 into @procName

while(@@fetch_status = 0)
begin

     Exec sp_Recompile @procName
     fetch c1 into @procName
end
close c1
deallocate c1
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10791824
Personally I would use the system tables in this case just for the extra performance.  I would, however, add WITH (NOLOCK), like this:

select name
 from sysobjects WITH (NOLOCK) where xtype = 'P'

as I do for all direct system table access, *especially* when used with cursors.
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10791825
hey arbert you still have to exclude the dt_% procedures or atleast I would think you should
0
 
LVL 34

Expert Comment

by:arbert
ID: 10792095
YOu can--no big deal if you don't.  It's a stored proc like anything else and will compile without error.  The point is, no matter what query you use, use the appropriate system proc or information_schema view to get it--don't use the system tables....
0
 
LVL 8

Expert Comment

by:plq
ID: 10792127
Here's one off the web. The fact that it is table driven instead of sp driven is nice. I dont want points for this

create proc sp_recompile_all
as
DECLARE table_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
order by name

OPEN table_cursor

DECLARE @tablename varchar(30)
FETCH NEXT FROM table_cursor INTO @tablename
WHILE (@@fetch_status -1)
BEGIN
select "Table = ", @tablename
EXEC ("sp_recompile " + @tablename)
FETCH NEXT FROM table_cursor INTO @tablename
END

print " "
print "********************************************"
PRINT "Recompile store procdures for all tables!"


CLOSE table_cursor
GO
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10793640
IMHO opinion unfair.  Jayrod should have gotten the bulk of the points since he posted the core part of the solution.
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10793656
well thanks scott :)

it was a team effort.
0
 
LVL 2

Author Comment

by:LJG
ID: 10793771
I think both of you deserve points.  JoyRod please check Points for  jayrod at

http://oldlook.experts-exchange.com:8080/Databases/Microsoft_SQL_Server/Q_20949225.html


Thanks
LJG
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now