Deprecated column types

Looking to the future, I'm examining an application for deprecated features. Data types text, ntext & image are marked for deprecation in a future version of sql (although they still appear to be present in SQL 2012).

Finding them is easy via the information_schema views -

SELECT table_catalog, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('text','ntext','image')

If you want more information you can use the system tables directly. -

For SQL2005+ -

SELECT a.name name, b.name, c.name, b.max_length, *
FROM sys.objects a
INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.system_type_id
WHERE a.type='u'  
AND c.name IN ('text','ntext','image')
ORDER BY 1,2,3

For SQL 2000 -

SELECT a.name tablename, b.name columnname ,c.name datatype, b.length, *
FROM sysobjects a  
INNER JOIN syscolumns b  ON a.id=b.id  
INNER JOIN systypes c  ON c.xtype=b.xtype
WHERE a.xtype='u'  
AND c.name IN ('text','ntext','image')
ORDER BY 1,2,3