• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

Changes to tables and Stored Procedures

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
LJG
Asked:
LJG
  • 10
  • 5
  • 5
  • +3
1 Solution
 
billy21Commented:
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
 
billy21Commented:
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
 
jayrodCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
billy21Commented:
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
 
billy21Commented:
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
 
billy21Commented:
You should include UDFs and Triggers too.
0
 
Scott PletcherSenior DBACommented:
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
 
billy21Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
billy21Commented:
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
 
billy21Commented:
Yep Scott.  Right you are.  I didn't know that.
0
 
jayrodCommented:
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
 
billy21Commented:
hmmm... Couldn't hurt to throw in an sp_updatestats too as part of your maintenance script.
0
 
billy21Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
jayrodCommented:
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
 
arbertCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
jayrodCommented:
hey arbert you still have to exclude the dt_% procedures or atleast I would think you should
0
 
arbertCommented:
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
 
plqCommented:
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
 
Scott PletcherSenior DBACommented:
IMHO opinion unfair.  Jayrod should have gotten the bulk of the points since he posted the core part of the solution.
0
 
jayrodCommented:
well thanks scott :)

it was a team effort.
0
 
LJGAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 10
  • 5
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now