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”

SP_Depends is very useful stored procedure which can give you list of the dependent object of your table or views. If you pass table name in SP_Depends, it will give you depended views, stored procedures, functions etc. let us look at this by small demo.

--create one small database for testing

create database DependCheck

go

use DependCheck;

go

--create one table and insert some data

create table TestDepend

(

ID Int Identity(1,1),

Name Varchar(20)

)

GO

Insert into TestDepend

SELECT 'RITESH' UNION ALL

SELECT 'RAJAN' UNION ALL

SELECT 'ALKA'

go

--create one PROC which is depend on TestDepend Table

CREATE PROC uspTestDependSelectAll

AS

SELECT * FROM TestDepend

GO

--try executing PROC

EXEC uspTestDependSelectAll

GO

--check SP_Depends system procedure to get all dependent objects

sp_depends 'TestDepend'

GO

--here is one alternate way to get dependent list

SELECT * FROM information_schema.routines ISR WHERE charindex('TestDepend', ISR.ROUTINE_DEFINITION)>0

GO

--USE master;

--GO

--DROP DATABASE DEPENDCHECK

--GO

Well we have seen two different ways to get list of dependent objects of tables but in my live database when I run both way, it gives me different results however in our case it will give same results. So the question I would like to ask is, which way is correct and reliable?

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