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
(
ID INTIDENTITY(1,1)
)
GO
–create one SPto return data of baseTable
create proc usp_getbaseTableData
as
select * from baseTable
GO
–check whetherSP_Depends returns any data or not
–I am sure, itwill return 🙂
sp_dependsusp_getbaseTableData
GO
–now drop thebasetable
drop table baseTable
GO
–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.
sp_dependsusp_getbaseTableData
GO
–now createbase table again with same structure
create table baseTable
(
ID INTIDENTITY(1,1)
)
GO
–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
sp_dependsusp_getbaseTableData
GO
–now you havetwo ways
–1.) drop andre-create SP
–2.) usesp_refreshsqlmodule.
EXEC sp_refreshsqlmodule ‘usp_getbaseTableData’
GO
–now you willagain get proper information
–after takingany 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 butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

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
(
ID INTIDENTITY(1,1)
)
GO
–create one SPto return data of baseTable
create proc usp_getbaseTableData
as
select * from baseTable
GO
–check whetherSP_Depends returns any data or not
–I am sure, itwill return 🙂
sp_dependsusp_getbaseTableData
GO
–now drop thebasetable
drop table baseTable
GO
–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.
sp_dependsusp_getbaseTableData
GO
–now createbase table again with same structure
create table baseTable
(
ID INTIDENTITY(1,1)
)
GO
–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
sp_dependsusp_getbaseTableData
GO
–now you havetwo ways
–1.) drop andre-create SP
–2.) usesp_refreshsqlmodule.
EXEC sp_refreshsqlmodule ‘usp_getbaseTableData’
GO
–now you willagain get proper information
–after takingany 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 butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

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

Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

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