Determine ANSI_PADDING for a table?

1) How do I determine the ANSI_PADDING setting for an existing table? (SQL
2000 or SQL 2005.)
Objectproperty doesn't show ANSI_PADDING, although it shows ANSI_NULLS.
2) How do I change the ANSI_NULLS or ANSI_PADDING setting for an existing
table?
These settings are just set with a seemingly "global" SET statement;
apparently the environment (or something in the ether) is set with this
setting, and then that is used when tables are created. The same is true
of options like NUMERIC_ROUNDABORT.
Thanks.
David Walker"DWalker" <none@.none.com> wrote in message
news:eWsmaohUIHA.4752@.TK2MSFTNGP05.phx.gbl...
> 1) How do I determine the ANSI_PADDING setting for an existing table?
> (SQL
> 2000 or SQL 2005.)
>
You can either use:
EXEC sp_help Foo (look at TrimTrailingBlanks)
or
SELECT [name], typestat
FROM syscolumns
WHERE id IN ( OBJECT_ID('Foo') )
ANSI_PADDING is ON when typestat is 2, 0 if OFF.
> 2) How do I change the ANSI_NULLS or ANSI_PADDING setting for an existing
> table?
>
You can use ALTER TABLE and setting the corresponding setting before the
alter statement, here is an example:
SET ANSI_PADDING OFF
GO
CREATE TABLE Foo(col1 VARCHAR(10) NOT NULL)
GO
INSERT Foo VALUES ('Test ')
GO
SET ANSI_PADDING ON
GO
ALTER TABLE Foo ALTER COLUMN col1 VARCHAR(10) NOT NULL
GO
INSERT Foo VALUES ('Test ')
GO
SELECT '|' + col1 + '|'
FROM Foo
GO
DROP TABLE Foo
And here is a note from BOL:
"In a future version of SQL Server ANSI_PADDING will always be ON and any
applications that explicitly set the option to OFF will produce an error.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature."
HTH,
Plamen Ratchev
http://www.SQLStudio.com