SP_RefreshSqlModule in SQL Server 2005/2008

According to the MSDN, SP_RefreshSQLModule:
Updates the metadata for the specified non schema-bound stored procedure, user-defined function, or view. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.
Rather than going into detailed theory session, let me give you one example to make it clear in your mind.
Suppose you have one table, named “baseTable” and you create one SP, ”usp_getBaseTableData” , which used to return all records of “baseTable” table.  ”usp_getBaseTableData” is now depend 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 drop your table “baseTable” and check the dependency of ”usp_getBaseTableData”, you won’t get anything but the message like this:
Object does not reference any object, and no objects reference it.
Obviously you will not get any reference as you have already dropped the table, now create the table with same name and structure and after that run SP_Dependsto check dependency and you will again get the same message, though you have created table and the same table name is being reference in SP. So if you will run 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 this before….
Now, to make reference of new “BaseTable” with ”usp_getBaseTableData”, you have to drop SP and recreate again. But this shouldn’t be the practical solution and that is why you have “SP_RefreshSQLModule” to refresh non schema-bound SPs or functions or views. Let us see how it works.
–create one table
create table baseTable
(
ID INTIDENTITY(1,1)
)
GO
–create one SP to return data of baseTable
create proc usp_getbaseTableData
as
select * from baseTable
GO
–check whether SP_Depends returns any data or not
–I am sure, it will return :)
sp_dependsusp_getbaseTableData
GO
–now drop the basetable
drop table baseTable
GO
–now if you try sp_depends, it will show you below message as you don’t “BaseTable”
–Object does not reference any object, and no objects reference it.
sp_dependsusp_getbaseTableData
GO
–now create base table again with same structure
create table baseTable
(
ID INTIDENTITY(1,1)
)
GO
–use the same command, it will again show you same message.
–though you have now table, if you will run your SP, it will work
–but sp_depends will not show you proper information
sp_dependsusp_getbaseTableData
GO
–now you have two ways
–1.) drop and re-create SP
–2.) use sp_refreshsqlmodule.
EXEC sp_refreshsqlmodule ‘usp_getbaseTableData’
GO
–now you will again get proper information
–after taking any of the previous suggestion in comment
sp_dependsusp_getbaseTableData
GO
BTW, personally I prefer “information_schema.routines” then “SP_Depends” to get dependency of object.
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.