Find all columns of type nvarchar(max) in all tables in a SQL Server database

Question

I found this question on stackoverflow that almost answers my question: Find all columns of a certain type in all tables in a SQL Server database

However I need to find all fields of type nvarchar(max) specifically. If I try this:

SELECT table_name [Table Name], column_name [Column Name]
FROM information_schema.columns where data_type = 'nvarchar(max)'

It doesn't work and nothing is returned. If I try this:

SELECT table_name [Table Name], column_name [Column Name]
FROM information_schema.columns where data_type = 'nvarchar'

It works but there are hundreds of results and I only care about fields of max size. How do I select all nvarchar(max) fields specifically?

Answer 1

The character_maximum_length will be -1 for max.

select 
    table_name as [Table Name]
  , column_name as [Column Name]
from information_schema.columns 
where data_type = 'nvarchar' 
  and character_maximum_length=-1