sql server (alternate): Hi I've got many tables that has columns definition TrimTrailingBlanks = NO to
all char and varchar columns.

How can I set TrimTrailingBlanks to Yes without re-creating the tables.

UPDATE <TableName>
SET TrimTrailingBlanks = 'Yes'

I doubt a simple update is what Ted is asking for. Ted to my knowledge
TrimTrailingBlanks is set at table creation based on the Ansi_padding
setting at that time. Sorry don't think MS has provided a way to change the
setting on a table after the creation. :( Hope I'm wrong....

Danny

[quoted text, click to view]

This is unsupported as it is manipulating system tables but here it is.

-- Set Allow updates to system tables ON
sp_configure 'allow',1
reconfigure with override

-- Turn on the "TrimTrailingBlanks" bit for tables listed....
-- Will SET TrimTrailingBlanks to YES for all columns in the table
-- you might want to be more selective by listing table and column name.

Update syscolumns
set typestat = typestat |2
where id in ( object_id('t1') )

-- TURN OFF allow updates
sp_configure 'allow',0
reconfigure



[quoted text, click to view]

[posted and mailed, posted and mailed]

Ted (txt@emia.com.au) writes:
[quoted text, click to view]

First, you should think twice before you take this path. TrimTrailingBlanks
= No, is the effect of the setting ANSI_PADDING being ON. ANSI_PADDING is
on by default for connection from most clients (DB-Library being the
exception.) Furthermore, ANSI_PADDING must be on when you are using
indexed views and indexed computer columns. There may be further situations
where ANSI_PADDING is needed in future versions of SQL Server.

That said, ALTER TABLE is what you can use:


SET ANSI_PADDING OFF
go
CREATE TABLE trimtrailer(a varchar(12) NOT NULL)
go
INSERT trimtrailer(a) VALUES ('Trimmer ')
go
SET ANSI_PADDING ON
go
ALTER TABLE trimtrailer ALTER COLUMN a varchar(12) NOT NULL
go
INSERT trimtrailer(a) VALUES ('Trimmer ')
go
SELECT '<' + a + '>' FROM trimtrailer
go
DROP TABLE trimtrailer

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at