Remove a column from a table value function after an underlying table has been altered

Question

I have a simple use case, where I'm creating a table filling it with some basic information and creating a table value function which in this scenario only returns a row if the name is 'Apple'.

Select * from FruitTable Where Name = 'Apple'

Everything works fine until I decide that I don't need the Active column anymore, and drop it from the Table. After re-running the function i get this error

-- Use the function it fails Msg 4502, Level 16, State 1, Line 1
-- View or function 'GetApples' has more column names specified than columns defined.

Select * from GetApples()

I've looked around and see that this is an occurrence with views if a table is altered, however with this function I'm selecting * from the FruitTable and expected that if any changes were made to the Fruit table whether adding or removing columns in the future that the Select * would be beneficial as I don't have to define the column structure of the resulting table.

Also I just performed an SP_Help on the function and noticed that it looks like even though I did a select * there actually is meta data stored about the columns.

SP_Help GetApples
--Column_name   Type
--Id            int
--Name          varchar
--Active        bit

So I have two questions.

1. Is there a way to drop the column from the function, or refresh the function in some way after the Alter?

2. Given that I'm running into these problems with select * in the table value function would it be better to actually define the resulting table column structure, or is there away to do what I wanted with the select * that would not require too much upkeep when altering the underlying table occurs?

Thanks

Here is the test SQL

-- Create the Simple Table Fill in Some Values
Create Table FruitTable(Id int, Name varchar(50), Active bit)
Insert Into FruitTable values 
(1 , 'Apple', 1),
(2 , 'Pear', 0),
(3 , 'Orange', 0)

GO

-- Create a Basic Function
Create Function GetApples()
Returns Table 
As
Return(
        Select * from FruitTable Where Name = 'Apple'
       )
GO

-- Use the function it works and returns 
--Id    Name    Active
--1    Apple    1
Select * from GetApples()
Go 

-- Drop Active
Alter Table FruitTable Drop Column [Active]
GO


-- Use the function it fails Msg 4502, Level 16, State 1, Line 1
-- View or function 'GetApples' has more column names specified than columns defined.

Select * from GetApples()
Go 

Answer 1

I think you should use a refresh module system function as shown below.

EXEC sys.sp_refreshsqlmodule 'dbo.GetApples';