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_Depends to 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 INT IDENTITY(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_depends usp_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_depends usp_getbaseTableData

GO

--now create base table again with same structure

create table baseTable

(

ID INT IDENTITY(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_depends usp_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_depends usp_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”