[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Changes to tables and Stored Procedures

Posted on 2004-04-09
24
Medium Priority
?
451 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 70

Expert Comment

by:Scott Pletcher
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 70

Expert Comment

by:Scott Pletcher
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 500 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
 
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 70

Expert Comment

by:Scott Pletcher
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 70

Expert Comment

by:Scott Pletcher
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 70

Expert Comment

by:Scott Pletcher
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 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