Question: How many Columns have VARCHAR (MAX), NVARCHAR (MAX), VARBINARY (MAX) and XML Data Type using INFORMATION_SCHEMA.COLUMNS & Value Character Maximum Length -1
Generally many SQL Users and Experts thinks about how to find out the how many columns have in the table that have MAX data type and most of the user thinks about that the length of any column can be 0 or more than Zero but VARCHAR(MAX), VARBINARY(MAX),NVARCHAR(MAX) and XML data type contain the -1 CHARACTER MAXIMUM LENGTH in SQL Server.
It is very important to know because MAX data type contain the large value but if you think about it is not necessary to be a MAX than you can reduce the length of your data type
To copy and run the following example in SSMS and see the how many columns are containing -1 length in one database?
--For Single Database using INFORMATION_SCHEMA.COLUMN
--For Single Table using INFORMATION_SCHEMA.COLUMN