Solved

Changes to tables and Stored Procedures

Posted on 2004-04-09
24
438 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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: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 69

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 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
 
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: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 69

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 69

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

831 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