Getting TEXT, NTEXT and IMAGE columns in Database

As per Microsoft documentation  https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql  TEXT , NTEXT and IMAGE column will be removed from future , and are all alternative data tyes

TEXT        will be        VARCHAR(MAX)

NTEXT      will be       NVARCHAR(MAX)

IMAGE      will be       VARBINARY(MAX)

this script is useful to see all these column in whole database and will display Table name , Column Name  and Data Type heading for better understanding , 

System Table    sys.types   is used to check  system_type_id for every data type, so we can use same script to find any specific data type columns in whole database by just changing value for system_type_id IN list

in above query 34,35,and 99 are system_type_id's for TEXT, NTEXT and IMAGE data types

NOTE: above query will display one extra table "dtproperties" which is because microsoft changed "XTYPE" to "U" in object types after SQL SERVER 7. just avoid that column