Wednesday, October 18, 2017

How to Update the Metadata of an Object in SQL Server

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:

Suppose we have a Table with two existing columns as follows :
Select*From Tbl_EMP;
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

CREATE FUNCTION dbo.Fn_Emp( )
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'

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 ()

Output :
Emp_ID Emp_Name
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'
GO

Now re-run below queries of the dependent View or Function


SELECT * FROM dbo.V_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


--------------------------------------------------------------------------------------------------------
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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog