Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rythm123us
Starting Member
27 Posts |
 Posted - 2005-09-20 : 09:43:19
|
| Is there a way to check syntax for all stored procedures?? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-20 : 09:49:50
|
| Check syntax?When create a procedure it will be checked and you will get error if anyDo you mean to check whether the objects used in sp are valid?MadhivananFailing to plan is Planning to fail |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-21 : 00:08:17
|
| You could ditch them all out to a directory with the DROP and CREATE options checked and write a little scripts use osql and cycle through all the files. Just record the output of each osql call and check for errors on completion. You could also create a copy of the database, drop all the stored procs, then use DTS to transfer in procs. It will give you a lot of what failed.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-21 : 04:51:17
|
| Might ALTER be safer than DROP/CREATE? Will keep the permissions and not drop the Sproc if the new one won't "compile"Interesting idea to flush out changes to DB structure that might impact on legacy SprocsKristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-21 : 19:29:55
|
| How is it safer? If they won't compile, you don't need them to CREATE.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-22 : 01:39:18
|
| "How is it safer"Yeah, you're right. What's there will be rubbish. However, it might be some subtle code path that is rubbish and leaving the original there might allow applications to run for a while, or even "most of the time", whilst the SProc is being fixed in Development.Kristen |
|
|
|
|
|
|
Checking Syntax for All Stored Procedures