How should I update the functions after changing the table columns?

Question

Let's assume I have a table with 2 columns: column1 and column2.

Now, I create a function based on the table as follows:

CREATE OR ALTER Function [dbo].[Fn_Test] ()
RETURNS TABLE
AS
    RETURN
        SELECT *
        FROM Table1

If later I add a third column to the table or remove one of the columns, my function does not get updated, and I have to alter my function again. How can I update all functions at once, or is there a way for the functions related to that table to be updated after changes are made to the table?

Answer 1

If later we add a third column to the table or remove one of the columns, my function does not get updated, and I have to alter my function again.

Yup.

How can I update all functions at once, or is there a way for the functions related to that table to be updated after changes are made to the table?

You'll need to bring your own tooling for code-generation, ideally within an SSDT (*.sqlproj) project, because things like that should be under source-control, and relatedly: no-one should be making any ad-hoc edits to database objects (like procedures, functions, views, etc) in a production/live database, and finally because by using SSDT (or similar tooling like SQLAlchemy) means you can use things like T4 to automatically generate the SELECT <column-list> (for views/functions/procdures) directly from your CREATE TABLE definitions.

...but none of this is automatic nor managed for you by MSSQL Server. When you use SSDT your changes get applied during a separate Publish process or via the Schema Compare feature.

---

In your case, where you want a FUNCTION to be kept in-sync with a TABLE, then you'd want something like this:

MyFunction.sql.tt (this is a T4 Template)

In your T4 Template's <#+ (template class defintiion block) you'd use EnvDTE and/or SqlCodeAnalysisRule to get your CREATE TABLE definitions and re-generate the function definition:

Here's an article from a while ago, but I think is still relevant, which uses EnvDTE to get CREATE TABLE definitions, which I've used as the basis for the T4 template below. See also this QA.

Note: I haven't updated the Visual Studio COM interop references from the 2014 article, so they'll be out-of-date for VS2022 today, but it's trivial to fix that and so is left-up as an exercise for the reader:

<#@ template language="C#" debug="true" hostspecific="true" #>
<#@ assembly name="Microsoft.VisualStudio.Shell.Interop.8.0" #>
<#@ assembly name="EnvDTE" #>
<#@ assembly name="EnvDTE80" #>
<#@ assembly name="VSLangProj" #>
<#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll" #>
<#@ assembly name="C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.dll" #>
<#@ assembly name="C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.Extensions.dll" #>
<#@ import namespace="Microsoft.VisualStudio.Shell.Interop" #>
<#@ import namespace="EnvDTE" #>
<#@ import namespace="EnvDTE80" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="Microsoft.SqlServer.Dac" #>
<#@ import namespace="Microsoft.SqlServer.Dac.Model" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".sql" #>
<#
    using TSqlModel model = this.CreateTSqlModel();

    TSqlTable myTable = RequireTable( model, "MyTable" );
 
#>

CREATE FUNCTION dbo.MyFunction() RETURNS TABLE
    WITH SCHEMABINDING
AS RETURN

SELECT
<#    foreach( Column c in myTable.Columns ) { /* See https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.model.column?view=sql-dacfx-162 */ #>
    t.<#= c.Name #>,
<#    } // foreach Column #>
FROM
    dbo.MyTable;

END;

<#+
 
    public TSqlModel CreateTSqlModel()
    {
        IServiceProvider hostServiceProvider = (IServiceProvider)this.Host;
        DTE              dte                 = (DTE)hostServiceProvider.GetService( typeof(DTE) );

        TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions { });

        IReadOnlyList<ProjectItem> tableItems = dte.Solution.Cast<Project>()
            .SelectMany( p => p.ProjectItems )
            .Where( i => i.Name.EndsWith( ".sql", StringComparison.OrdinalIgnoreCase ) )
            .Where( i => i.FileCount == 1 && i.FileNames[0].Contains( "Tables" ) ) )
            .ToList();

        foreach( ProjectItem tableItem in tableItems )
        {
            String sqlText = File.ReadAllText( tableItem.FileNames[0] );
            if( sqlText.Contains( "CREATE TABLE" ) )
            {
                model.AddObjects( sqlText );
            }
        }

        return model;
    }

    public static TSqlTable RequireTable( TSqlModel model, String tableName )
    {
        return model.GetObjects<TSqlTable>( DacQueryScopes.All, ModelSchema.Table )
            .Cast<TSqlTable>()
            .Where( t => t.Name == tableName )
            .Single();
    }

#>