August 10, 2009 at 6:19 am
Would be grateful for any assistance which may be added.
August 10, 2009 at 7:12 am
You can run the select statement bellow that will produce the alter table statements for you. Notice that this will do only part of the job. For example if there are indexes on constraints that are based on those columns, you’ll have to drop them first and recreate them after you alter the column. In any case, I think that this can get you started.
SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + case when DATA_TYPE = 'nvarchar' THEN ' varchar (' ELSE 'char (' END
+ CONVERT(VARCHAR(4),CHARACTER_MAXIMUM_LENGTH) + ')' + CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL ' END
FROM INFORMATION_SCHEMA.columns
WHERE DATA_TYPE IN ('nvarchar', 'nchar')
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 10, 2009 at 7:28 am
so you just need a script to dynamically create the UPDATE TABLENAME SET COLNAME = RTRIM(COLNAME) script, correct?
something like this:
SELECT 'UPDATE [' + object_name(object_id) + '] SET [' + name + '] = RTRIM([' + name + '])'
from sys.columns where TYPE_NAME(user_type_id) IN ('varchar','nvarchar')
i think when you changed the datatype from char to nvarchar, that event happened automatically, so there's no need to do it again...but here you go.
Lowell
August 10, 2009 at 7:30 am
Adi I read the requirement as he alreyad changed the columns from char to varchar, and now wanted to remove trailing spaces:
Note, the length of the data type should not be changed, only
content.
I did something like you did...changing every datatype to it's max used size....then re-read teh post, and changed my approach.
great minds think alike and all that.
Lowell
August 10, 2009 at 7:46 am
Lowell (8/10/2009)
Adi I read the requirement as he alreyad changed the columns from char to varchar, and now wanted to remove trailing spaces:Note, the length of the data type should not be changed, only
content.
I did something like you did...changing every datatype to it's max used size....then re-read teh post, and changed my approach.
great minds think alike and all that.
After I reread the original message, I have to agree that you are correct and my statement was not what the OP was asking for. One thing I don’t understand. If the data type that is used is char, then no matter how long the value in the column, this column uses the maximum length that it was defined to store. If the column’s data type is varchar, then it is already using the length of the value that it stores plus 2 bytes overhead that it needs in order to manage the column. My question is – what can the OP gain by running an update statement?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 10, 2009 at 7:56 am
i ran this test below...he's right...if you had a column defined as char(200), for example, stuck data in it, then changed the column to varchar(200), the datalength is still 200...even though it has trailing spaces.
I had expected the varchar to auto-trim, but it doesn't:
create table charexample (charid int identity(1,1),charfield char(200) )
insert into charexample
select 'one' union all
select 'two' union all
select 'three'
select *,datalength(charfield) as ln from charexample --200
alter table charexample alter column charfield varchar(200)
select *,datalength(charfield) as ln from charexample --still 200
update charexample set charfield=rtrim(charfield)
select *,datalength(charfield) as ln from charexample --much smaller
Lowell
August 10, 2009 at 8:22 am
Nice demo. Defiantly proves the point. I have to admit that it did surprise me, but every day I learn something(s) new
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 10, 2009 at 9:51 am
Amir
I need to write a procedure that trimming tables that are created in the database
If you mean to reduce the size of the table in the database changing from CHAR to VARCHAR may not reclaim the amount of space you are thinking it will.
I highly recommended that you read this SSC article on the effect of meta data changes.
Changing Table Schema – what goes behind the scenes – Part II
http://www.sqlservercentral.com/articles/Design+and+Theory/67553/
and the first article in the series
Changing Table Schema – what goes behind the scenes – Part I
http://www.sqlservercentral.com/articles/Design+and+Theory/67552/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply