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
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.