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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-28 : 08:06:42
|
| ahmed writes "hi all,i'm running into a problem with sql 7 on windows 2000. i'm using a custom stored procedure to determine what columns our code base is currently using.the idea is simple: by deleting one column at a time, compiling all procedures, and detecting whether an error was encountered. if an error was found, i assume we're using the column. if not, we're not.the problem arises because there seems to be an inconsistency in how/when compiling creating stored procedures error out. sometimes they fail, and sometimes not, throwing all my results off.any ideas? is there any setting/system stored procedures that affects how stored procedures are compiled upon creation? any way to recompile stored procedures?any help will be appreciated.thanks!ahmed" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-28 : 08:31:35
|
| You can use DBCC FLUSHPROCINDB to recompile all stored procedures. It works like this:DECLARE @dbid intSET @dbid=DB_ID('myDatabase')DBCC FLUSHPROCINDB(@dbid) --no, you can't put the DB_ID() funtion directly in hereThis is an undocumented function (well, it's undocumented if you don't have The Guru's Guide To Transact-SQL by Ken Henderson!) You should also run this command to clear the procedure cache before you recompile all sprocs:DBCC FREEPROCCACHEThat will ensure that the old plans are wiped from the cache and the new plans will be used.FWIW, the approach you're using is not a good one. Dropping a column and then testing whether something breaks won't help you fix it later. Why were the columns put into the table(s) if they're not being used? And just because a column doesn't appear by name in a stored procedure, doesn't mean it isn't being used...what about SELECT * ? What about views? Or external ad-hoc queries?I imagine the original table designer is not available, and that you don't have documentation (otherwise this wouldn't be an issue). If that's true, then you absolutely SHOULD NOT just arbitrarily alter tables to see if they break. |
 |
|
|
|
|
|
|
|
compiling stored procedures