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

--------------------------------------------------------------------------------------------------------