March 20, 2017 at 3:13 pm
Hi All,
We have 100 tables in our database and I need to modify one datatype from Text to VARCHAR(MAX). I need to write one script to affect whole database.
I am tring to run below code but It did not work. I need help to correct this code in alter datatype from Text to VARCHAR(max).
Thanks a lot in advance.
USE [Test]
SELECT 'Alter table' + TAble_name +
' SET DATATYPE = VARCHAR(MAX)' +
' WHERE DATA_TYPE = "TEXT" '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'TEXT'
March 21, 2017 at 2:37 am
First, you may not have TEXT columns in your database.
Second, your query is not correct.
Try this
SELECT t.name,c.name,tp.name,tp.max_length,
'ALTER TABLE '+QUOTENAME(SCHEMA_NAME(t.schema_id))+'.'+QUOTENAME(t.name)+' ALTER COLUMN '+c.name+' VARCHAR(MAX)' [alter_column]
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id=c.object_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
WHERE tp.name='text'
Igor Micev,My blog: www.igormicev.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply