Can probably script the stored procedure calls out with SET NOEXEC ON, so it doesn't actually run anything... This would allow you to catch a lot of basic errors (invalid objects, missing fields, etc.)

Is that along the lines of what you were talking about?

If so, there's a CLR mentioned in SQL Mag that seems to do what you're looking for.

Not in TSQL, but here is a piece of C# that does that for me. Requires SMO - you'd need to reference the following assemblies, paths being relative to C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft :

• SQLCommon\Microsoft.SqlServer.Smo.dll
• SQLCommon\Microsoft.SqlServer.ConnectionInfo.dll
• SQLDB\DAC\Microsoft.Identity.Client.dll
• SQLDB\DAC\Microsoft.Data.SqlClient.dll

And also copy Microsoft.Data.SqlClient.SNI.x64.dll and/or Microsoft.Data.SqlClient.SNI.x86.dll to bin\Debug.

The idea is to scroll through stored procedures and run a trivial ALTER PROC on each, providing the same text as it was before. This is enough to catch "missing field" or "ambiguous field" errors.

Could be done without SMO as well, with some system view querying, but this also works.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace AlterAllSPs
{
    internal class Program
    {
        static void Main(string[] args)
        {
            SqlConnectionInfo ci = new SqlConnectionInfo("MyServer")
                { DatabaseName = "MyDatabase" };
            ServerConnection conn = new ServerConnection(ci);
            Server srv = new Server(conn);
            srv.SetDefaultInitFields(typeof(StoredProcedure), true); //Fetch SP data by default
            Database db = srv.Databases["MyDatabase"];

            foreach(StoredProcedure sp in db.StoredProcedures)
            {
                if(!sp.IsSystemObject)
                {
                    string h = sp.TextHeader;
                    if (h.StartsWith("CREATE "))
                        h = "ALTER " + h.Substring(7);
                    else //Comments followed by CREATE PROC
                        h = h.Replace("\r\nCREATE ", "\r\nALTER ");
                    try
                    {
                        conn.ExecuteNonQuery(h + sp.TextBody);
                    }
                    catch(Exception exc)
                    {
                        Console.Error.WriteLine($"{sp.Name}: {exc.InnerException.Message}");
                    }
                }
            }
        }
    }
}