Show deprecated SQL Server Data Types

From SQL Server version 2008 forward the data types text, ntext and image have been deprecated. The code below will cursor through all the user databases on your server and bring back the database, tables and columns that use these data types, if they exist.

-- Find Deprecated datatypes in all databases

USE master;

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#DataTypes')) DROP TABLE #DataTypes

CREATE TABLE #DataTypes(

    DBName VARCHAR(50)

    ,TableName VARCHAR(100)

    ,ColName VARCHAR(100)

    ,ExistingDataType VARCHAR(20)

    ,ChangeToDataType VARCHAR(20)

    )

Declare @DBName VARCHAR(50);

Declare @SQL VARCHAR(MAX);

DECLARE DeprecatedDTCur CURSOR FOR

        SELECT Name from sysdatabases where name not in ('master','model','msdb','tempdb') order by name

OPEN DeprecatedDTCur

FETCH NEXT FROM DeprecatedDTCur INTO @DBName;

WHILE @@FETCH_STATUS = 0

BEGIN

       PRINT 'Processing Database: ' + @DBName;

         SET @SQL = 'USE [' + @DBName + '];

         INSERT #DataTypes

         SELECT

                DB_Name() AS DBName

                ,O.NAME  AS TableName

                 ,col.NAME AS ColName

                 ,systypes.NAME    AS ExistingDataType

                 ,CASE systypes.NAME

                       WHEN ''text'' THEN  ''VARCHAR(MAX)''

                       WHEN ''nText'' THEN ''nVARCHAR(MAX)''

                       WHEN ''Image'' THEN ''VARBINARY(MAX)''

                 END AS ChangeToDataType

FROM syscolumns col

INNER JOIN sysobjects O

       ON col.id = O.id

INNER JOIN systypes

       ON col.xtype = systypes.xtype

WHERE O.Type = ''U''

       AND ObjectProperty(o.ID, N''IsMSShipped'') = 0

       AND systypes.NAME IN (

              ''text''

              ,''ntext''

              ,''image''

              )

ORDER BY O.NAME

       ,Col.NAME

          '

          --PRINT (@SQL)

          EXEC (@SQL)

       

        FETCH NEXT FROM DeprecatedDTCur INTO @DBName;

END;

CLOSE DeprecatedDTCur;

DEALLOCATE DeprecatedDTCur;

SELECT * FROM #DataTypes;

<end>