March 20, 2017 at 3:23 pm
Hi,
I am trying to alter datatype from TEXT to VARCHAT for multiple table in same database and I have used below code but it did not work.
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'
Thanks,
Unnati
March 20, 2017 at 4:13 pm
unnati.patel513 - Monday, March 20, 2017 3:23 PMHi,
I am trying to alter datatype from TEXT to VARCHAT for multiple table in same database and I have used below code but it did not work.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'
Thanks,
Unnati
What about schemas? You maybe would want to qualify the tables with schemas. What about the nullability of the columns?
Hope you are just generating the scripts for testing but the syntax should be alter table..alter column.
So from your code above, it would need to be something like:
USE [Test]
SELECT 'Alter TAble ' + Table_name +
' alter column ' + column_name + ' VARCHAR(MAX)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'TEXT'
Sue
March 20, 2017 at 4:58 pm
You need to read the ALTER TABLE syntax. The complexity is in repeating the existing column names, nullability, etc. I think Sue has a good basic script for you.
March 21, 2017 at 8:56 am
Please don't create multiple threads for the same topic. It fragments the conversation. https://www.sqlservercentral.com/Forums/1865681/Alter-datatype-in-multiple-colums
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2017 at 9:20 am
You can easily extend the excellent start from Sue with something like this. I also added brackets around table and column names in case you have spaces or reserved names.
SELECT 'Alter Table [' + Table_name +
'] alter column [' + column_name + '] VARCHAR(MAX) ' + case when IS_NULLABLE = 'NO' then 'NOT ' else '' end + 'NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'TEXT'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply