October 3, 2011 at 3:17 am
Hello,
I need a script to change the type of a column from char(n) to nvarchar(n) where n is the length and also do a right trim on the data. I can do this using the following script:
ALTER TABLE [tablename]
ALTER COLUMN [columnname] nvarchar(n)
UPDATE [tablename] SET [columnname] = RTRIM([columnname])
and this works fine but the problem is that the database contains a lot of tables and every table has a lot of columns.
I believe i have to make this steps
1. Loop through all tables of the database
2. For every table loop through all columns
3. Test if the column type is char
4. Store its length on a temp var
5. change type from char to nvarchar
6. make a rtrim on the current column of the current table
i don't know how to implement these steps(if they are correct and if it's possible) in T-sql.
Thank you.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply