SQL Server : get messages from referenced entities procedure in code

Question

I'm running big dependency scan on legacy db and see that some objects have obsolete ref links, if you run this code in SSMS for View that points to not existing table like in my case, you will get your output on Results tab AND error info in Messages . Like in my case below.

I tried to check all env things I know and output of this stored procedure, but didn't see any indication.

How I can capture this event as I'm running this in looped dynamic SQL script and capture output in my table for further processing?

Updated:

1. it just text in Message box ,on error, you still have output on
   Results tab
2. this is sp, it loop thru object list I took from sys.object and run this string as my sample to get all dependencies, load all into table. This call to sql_reference_entities is the only way to get inter database
   dependency on column level. So I need stick to this 100$>

--

Select * 
From  sys.dm_sql_referenced_entities('dbo.v_View_Obs_Table','Object')

--

Answer 1

----update------

This behavior was fixed in SQL Server 2014 SP3 and SQL Server 2016 SP2:

Starting from Microsoft SQL Server 2012, errors raised by sys.dm_sql_referenced_entities (such as when an object has undergone a schema change) cannot be caught in a TRY...CATCH Transact-SQL block. While this behavior is expected in SQL Server 2012 and above, this improvement introduces a new column that's called is_incomplete to the Dynamic Management View (DMV).

KB4038418 - Update adds a new column to DMV sys.dm_sql_referenced_entities in SQL Server 2014 and 2016

----update-------

The tldr is that you can't capture these on the server side, and must use a client program in C#, PowerShell or some other client that can process info messages.

That DMV is doing something strange that I don't fully understand. It's generating errors (which a normal UDF is not allowed to do), and those errors do not trigger a TRY/CATCH block or set @@error. EG

create table tempdb.dbo.foo(id int)
go
create view dbo.v_View_Obs_Table
as

select * from tempdb.dbo.foo

go

drop table tempdb.dbo.foo
go
begin try
   Select * From  sys.dm_sql_referenced_entities('dbo.v_View_Obs_Table','Object')
end try
begin catch
  select ERROR_MESSAGE(); --<-- not hit
end catch  

However these are real errors, as you can see running this from client code:

using System;
using System.Data.SqlClient;

namespace ConsoleApp6
{


    class Program
    {
        static void Main(string[] args)
        {

            using (var con = new SqlConnection("Server=.;database=AdventureWorks;integrated security=true"))
            {
                con.Open();
                con.FireInfoMessageEventOnUserErrors = true;


                con.InfoMessage += (s, a) =>
                {
                    Console.WriteLine($"{a.Message}");
                    foreach (SqlError e in a.Errors)
                    {
                        Console.WriteLine($"{e.Message} Number:{e.Number} Class:{e.Class} State:{e.State} at {e.Procedure}:{e.LineNumber}");
                    }

                };

                var cmd = con.CreateCommand();

                cmd.CommandText = "Select * From  sys.dm_sql_referenced_entities('dbo.v_View_Obs_Table','Object')";
                           
                using (var rdr = cmd.ExecuteReader())
                {
                    
                    while (rdr.Read() || (rdr.NextResult() && rdr.Read()))
                    {
                        Console.WriteLine(rdr[0]);
                    }
                    
                }

                Console.ReadKey();


            }
        }
    }
}

outputs

Invalid object name 'tempdb.dbo.foo'.
Invalid object name 'tempdb.dbo.foo'. Number:208 Class:16 State:3 at v_View_Obs_Table:4
0
The dependencies reported for entity "dbo.v_View_Obs_Table" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity.  Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
The dependencies reported for entity "dbo.v_View_Obs_Table" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity.  Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist. Number:2020 Class:16 State:1 at :1