How to find type mismatches (implicit conversion) between sprocs and tables?

I have a sproc that has these lines

DECLARE @newTimestamp datetime = CURRENT_TIMESTAMP
UPDATE  dbo.Accounts
SET     Modified = @newTimestamp

However, in the Accounts DB, we have this

[Modified]         DATETIME2 (7) NOT NULL,

And then whenever we do comparisons (particular for equality), we don't get the desired answers. We have worked around this by setting the compatibility level low enough (110) to not trip on this, but is there any easy way to find any instances where we're mismatching data types in our sprocs vs our tables?

I'd rather not have to write a parser, but it seems like if I had to, I'd trawl through every table definition and record the column types, then find every use of each column and try to determine if it's being set or compared to a variable declared as a different type.

Are there any tools out there that already do this, or is there a complaint somewhere in a system view or log?

I've found that Brent Ozar's sp_blitzcache can probably find this in the query plans, but there's no guarantee that the cache will have every instance of this for a given DB; I think I'd feel more comfortable with a static analysis of the code instead of a live look at a running SQL server. I've got access to (many) live copies of the DBs, as well as the source code for the SQL solution in Visual Studio.

Edit - not really relevant to my question, but I can give the background. When we have a datetime, it has 3 digits accuracy after the decimal point. When we have a datetime2, it has 7 digits accuracy. If you take a single timestamp, and try to compare a datetime and a datetime2 of the same moment, they won't be equal.

If we fix our compatibility level to 110, it ignores the extra accuracy of the datetime2, which is our current behavior.

You can use the DMVs to view the query plan cache, and use XQuery on the plan's XML to filter for implicit conversion
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cplan.usecounts,
cplan.objtype,
qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
WHERE qplan.query_plan.exist('//Convert[@Implicit="1"]') = 1;


You can do the same for Query Store, which will keep plans in between service restarts.
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
v.query_plan
FROM sys.query_store_plan AS cplan
CROSS APPLY (VALUES(CAST(query_plan AS xml))) v(query_plan)
WHERE v.query_plan.exist('//Convert[@Implicit="1"]') = 1;


StackExchange Database Administrators Q#300686, answer score: 2