October 3, 2011 at 4:47 am
You could use the INFORMATION_SCHEMA.COLUMNS
SELECT
'ALTER TABLE [' + Table_Schema+'].['+Table_Name
+'] Alter Column ['+Column_Name+'] varchar('
+Convert(nvarchar(5),Character_Maximum_Length)+');'
+ ' Update ['+ Table_Schema+'].['+Table_Name
+ '] SET ['+Column_Name+']= Rtrim(['+Column_Name+']);'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE='CHAR'
That will output a list of all columns and write the Atler table script and update.
Set the Output window in ssms to text and you have your script to alter all the columns.
You could change it so that the elect output a string into a cursor and iterate through.
EDIT : SQL format error.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2011 at 5:54 am
Hello Jason,
Thank you very much.
The script generated from your code is perfect.
😉
October 3, 2011 at 5:59 am
No problem. 😀
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply