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_Depends
to 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 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
EXEC sp_refreshsqlmodule ‘usp_getbaseTableData’
–now you willagain get proper information
–after takingany of the previous suggestion in comment
Reference: Ritesh Shah
Ask me any SQL Server related question at my “ASK Profile”