SP_RefreshSqlModule in SQL Server 2005/2008

According to the MSDN, SP_RefreshSQLModule:
Updates the metadata for the specified non schema-boundstored procedure, user-defined function, or view. Persistent metadata for theseobjects, such as data types of parameters, can become outdated because ofchanges to their underlying objects.
Rather than going into detailed theory session, let me giveyou one example to make it clear in your mind.
Suppose you have one table, named “baseTable” and you createone SP, ”usp_getBaseTableData” , which used to return all records of “baseTable”table.  ”usp_getBaseTableData” is nowdepend on ”BaseTable”. SQL Server used to store this relational information in “sysdepends”.  In “sysdepends”,information gets stored with Object_ID rather than Object_Name so if you dropyour table “baseTable” and check the dependency of ”usp_getBaseTableData”, youwon’t get anything but the message like this:
Object doesnot reference any object, and no objects reference it.
Obviously you will not get any reference as you have alreadydropped the table, now create the table with same name and structure and afterthat run SP_Dependsto check dependency and you will again get the same message, though you havecreated table and the same table name is being reference in SP. So if you willrun your SP, it will work fine now as it will find the object but  SP_Depends won’t be able to find new object as new “baseTable”would have different Object_ID than older one.
Surprised!!!!!…. No, you shouldn’t if you have tried thisbefore….
Now, to make reference of new “BaseTable” with ”usp_getBaseTableData”,you have to drop SP and recreate again. But this shouldn’t be the practicalsolution and that is why you have “SP_RefreshSQLModule” to refresh nonschema-bound SPs or functions or views. Let us see how it works.
–create onetable
create table baseTable
–create one SPto return data of baseTable
create proc usp_getbaseTableData
select * from baseTable
–check whetherSP_Depends returns any data or not
–I am sure, itwill return 🙂
–now drop thebasetable
drop table baseTable
–now if you trysp_depends, it will show you below message as you don’t “BaseTable”
–Object doesnot reference any object, and no objects reference it.
–now createbase table again with same structure
create table baseTable
–use the samecommand, it will again show you same message.
–though youhave now table, if you will run your SP, it will work
–but sp_dependswill not show you proper information
–now you havetwo ways
–1.) drop andre-create SP
–2.) usesp_refreshsqlmodule.
EXEC sp_refreshsqlmodule ‘usp_getbaseTableData’
–now you willagain get proper information
–after takingany of the previous suggestion in comment
BTW, personally I prefer “information_schema.routines”then “SP_Depends”to get dependency of object.
Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
Ask me any SQL Server related question at my “ASK Profile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: