SQL SERVER sp_refreshsqlmodule to Update the Metadata of an Object
Whenever you modify the underlying objects (Tables, columns, datatypes etc) of any non-schema-bounded Stored Procedure, View or an User-defined Function, mostly due to customization then your object will not address those customization and might give you error or unexpected results.
To overcome this issue we need to refresh the Metadata of the underlying object whenever you do some alteration to them, as explained below:
Emp_ID Emp_Name
1 A1
2 B2
Whenever you modify the underlying objects (Tables, columns, datatypes etc) of any non-schema-bounded Stored Procedure, View or an User-defined Function, mostly due to customization then your object will not address those customization and might give you error or unexpected results.
To overcome this issue we need to refresh the Metadata of the underlying object whenever you do some alteration to them, as explained below:
Suppose we have a Table with two existing columns as follows :
Select*From Tbl_EMP;
GO
GO
Emp_ID Emp_Name
1 A1
2 B2
Now based on the above table, I created a non schema-bounded View and a Function as follows :
CREATE VIEW dbo.V_Emp
AS
Select*From dbo.Tbl_EMP
GO
AS
Select*From dbo.Tbl_EMP
GO
CREATE FUNCTION dbo.Fn_Emp( )
RETURNS TABLE
AS
RETURN
(Select*From dbo.Tbl_EMP)
GO
RETURNS TABLE
AS
RETURN
(Select*From dbo.Tbl_EMP)
GO
-----------------------------------------------------------
Later after a few days, I have modified my based table, where I added a new Column 'Address' with data as follows ..
ALTER TABLE Tbl_EMP Add Address Varchar(50)
Update dbo.Tbl_EMP Set Address='Bangalore' Where Emp_NAME ='A1'
Update dbo.Tbl_EMP Set Address='Hyderabad' Where Emp_NAME ='B2'
Update dbo.Tbl_EMP Set Address='Hyderabad' Where Emp_NAME ='B2'
Now the underlying/base table "Tbl_EMP" has three columns.If you run below queries of the dependent View or Function, you will get the output as follows :
SELECT * FROM dbo.V_Emp
SELECT * FROM dbo.Fn_Emp ()
SELECT * FROM dbo.Fn_Emp ()
Output :
Emp_ID Emp_Name
1 A1
2 B2
1 A1
2 B2
The Newly added Column 'Address' is not shown in the output , as these are not refreshed with the Meta Data.
-----------------------------------------------------------
Now we need to refresh the Meta data for the dependent Function and View as follows :
EXEC sys.sp_refreshsqlmodule 'dbo.Fn_Emp'
EXEC sys.sp_refreshsqlmodule 'dbo.V_Emp'
EXEC sys.sp_refreshsqlmodule 'dbo.V_Emp'
GO
Now re-run below queries of the dependent View or Function
SELECT * FROM dbo.V_Emp
SELECT * FROM dbo.Fn_Emp ()
SELECT * FROM dbo.Fn_Emp ()
Now we will get the refreshed output as it is from base table :
Output :
Emp_ID Emp_Name Address
1 A1 Bangalore
2 B2 Hyderabad
1 A1 Bangalore
2 B2 Hyderabad
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.