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?


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.