Technical Article

Generate alter datatype script for entire database

,

Step 1: Run the script in Part 1 and identify the column data type and property (NULL / NOT NULL)  and number of columns to be changed.

Step 2: Select the target database and run Part 2, which will generate the script required.

email me incase you need more information: cusvenus@gmail.com

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 !!!!

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating