Column types analysis of columns used in joins Is it possible to do a deep type analysis on the source code base, views and stored procedures in order to identify joins of columns that don't have compatible types?

I inherited a database that hasn't been consistent in using varchar vs nvarchar columns. And sometimes numeric columns are joined with text fields that contain numbers. Now there are some changes that bring more data in some of the tables in the database as in tens of millions of records and any discrepancies in the joined types can have huge performance impacts. These tables have varchar fields and it is not possible to change them to nvarchar (they are populated by a third party product that doesn't support nvarchar). 

The options that I have:


Convert all the varchar columns to nvarchar. Unfortunately, it is not possible. That would have been my preferred option. 
1.b Convert all the nvarchar columns to varchar. Hmmm... I feel this is a rabbit hole I probably don't want to get into though all the text data is supposed to be in the English language.  
Use the MS Transact SQL parser (DacFx) to analyse the queries, however, I feel this is going to take some significant programming effort. I would need a complete list of all sql server functions and the types they return. 


Any other ideas?

Thanks
Is it possible to do a deep type analysis on the source code base, views and stored procedures in order to identify joins of columns that don't have compatible types?


It's likely that is possible, but I'd like to recommend a different approach.


  ...and any discrepancies in the joined types can have huge performance impacts.


Rather than trying to comprehensively fix all of these mismatches, I would try and track down the ones that will give you the "biggest bang for your buck."  Use the sp_BlitzCache stored procedure from the SQL Server First Responder Kit.  You can run it like this:

EXEC sp_BlitzCache @DatabaseName = N'YourDatabaseName';


By default, this will give you the 10 most CPU-intensive queries from that database.

In the output, there is a "warnings" column, and you should be able to find queries that have these mismatched predicates and join columns by looking for "implicit conversion:"



For rows that have that warning, you can scroll over to the "Implicit Conversion Info" column for details, grab the execution plan from the "Query Plan" column, or just use the "Query Text" column to guide you to the right spot.

My main point here is that some of the mismatched data types might not be causing any real problems.  You don't want to waste time fixing those if the server is on fire because of a handful of specific columns in specific tables.