Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Changes to tables and Stored Procedures

Posted on 2004-04-09
24
Medium Priority
?
449 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

609 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