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>
Comments
Post a Comment