Generate alter datatype script for entire database

Refer to the below link for deprecation features:

http://msdn.microsoft.com/en-us/library/ms187993.aspx

Script to check how many columns are there with image data type in your database:

PART 1
======

SELECT OBJECT_NAME(c.OBJECT_ID) tablename, c.name columnname, 'isnullable' = case
WHEN c.is_nullable = 1 THEN 'NULL'
ELSE ' NOT NULL' END
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'image'  -- data type that has to be changed.
ORDER BY c.OBJECT_ID;

Now put this in a script to collect the table name, column name and is nullable property in the script.


PART 2
======

DECLARE @tablename VARCHAR(70)
DECLARE @columnname VARCHAR(256)
DECLARE @isnullable VARCHAR(256)
DECLARE @sqlexec VARCHAR(500)
DECLARE db_cursor CURSOR FOR
SELECT OBJECT_NAME(c.OBJECT_ID) tablename, c.name columnname, 'isnullable' = case
WHEN c.is_nullable = 1 THEN 'NULL'
ELSE ' NOT NULL' END
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'image'
ORDER BY c.OBJECT_ID;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename,@columnname,@isnullable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlexec = 'alter table ' + @tablename + ' alter column ' +  @columnname + ' varbinary(max) ' + @isnullable
print @sqlexec
FETCH NEXT FROM db_cursor INTO @tablename,@columnname,@isnullable
END
CLOSE db_cursor
DEALLOCATE db_cursor

This will generate a script which can be executed on the target database to change the data type.

Yes this can be used for other data types also.

Hope this helps !!!!