This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the use sp_refreshsqlmodulem system stored procedure which is for refreshing the Module Metadata information in SQL Server.
Whenever we are changing the structure of underlying objects, parent object will not refresh by those changes and may give you wrong output/result.
Check the below demonstration, and test it yourself:
Create sample table with data:
|
1 2 3 4 |
CREATE TABLE tbl_testRefresh (id int) GO INSERT INTO tbl_testRefresh VALUES (1),(2),(3) GO |
Create a table valued function:
|
1 2 3 4 5 6 |
CREATE FUNCTION fn_testRefresh () RETURNS TABLE AS RETURN (SELECT * from tbl_testRefresh) GO |
Check the function output:
|
1 2 |
SELECT * FROM dbo.fn_testRefresh() GO |
Result:
|
1 2 3 4 5 |
id ----------- 1 2 3 |
Now, add a column in table:
|
1 2 |
ALTER TABLE tbl_testRefresh ADD Name VARCHAR(10) GO |
Check the table output:
|
1 2 |
SELECT *FROM tbl_testRefresh GO |
Result:
|
1 2 3 4 5 |
id Name ----------- ---------- 1 NULL 2 NULL 3 NULL |
Now, check the function output:
In the result, the newly added column is not present which is wrong.
|
1 2 |
SELECT * FROM dbo.fn_testRefresh() GO |
Result:
|
1 2 3 4 5 |
id ----------- 1 2 3 |
Now, refresh the sql modules:
|
1 2 |
EXEC sys.sp_refreshsqlmodule 'dbo.fn_testRefresh' GO |
Now, check the function output:
|
1 2 |
SELECT * FROM dbo.fn_testRefresh() GO |
Result:
|
1 2 3 4 5 |
id Name ----------- ---------- 1 NULL 2 NULL 3 NULL |
Leave a Reply