IF OBJECT_ID('tempdb.dbo.#DBDATATYPES') IS NOT NULL

DROP TABLE #DBDATATYPES

CREATE TABLE #DBDATATYPES

(

DbName nvarchar(128)

,TableName nvarchar (128)

,ColumnName nvarchar (128)

,Datatype nvarchar (128)

)

INSERT INTO #DBDATATYPES

EXEC sys.sp_MSforeachdb '

IF ''?'' IN (''master'', ''msdb'')

RETURN;

USE [?]

SELECT ''?'' AS DatabaseName, OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName, t.name

FROM sys.columns AS c

INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id

WHERE t.name IN (''image'', ''ntext'', ''text'')

ORDER BY TableName, ColumnName'

SELECT * FROM #DBDATATYPES

ORDER BY DbName, TableName, ColumnName

DROP TABLE #DBDATATYPES