List TrimTrailingBlanks for all columns in all tables?

Hi.  
If I execute "sp_help Table_1" I see the extended properties of the columns, like:
Column_name		Type	Computed	Length	Prec	Scale	Nullable	TrimTrailingBlanks	FixedLenNullInSource	Collation
SomeTextCHAR		nchar	no		60	   		yes		(n/a)			(n/a)			SQL_Latin1_General_CP1_CI_AS
SomeTextVARCHAR	varchar	no		50	   		yes		no			yes			SQL_Latin1_General_CP1_CI_AS

Open in new window

I would like to execute a query that will list all the extended properties of all the columns in all the tables in my database, or rather specifically the "TrimTrailingBlanks" property.
Is this possible?
Thanks
Rob
Rob RudloffIT Development SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Well, that is controlled by ANSI_PADDING and is pretty much set at time of creating your table.
set ansi_padding off
go
create table ee_sample_table (vColumn varchar(50) null)
go
sp_help ee_sample_table   -- has trimtrailingspaces = YES   
go
--Padding on, so TrimTrailingBlanks is set to 'no'
set ansi_padding on
go
alter table ee_sample_table alter column vcolumn varchar(50)  null
go
sp_help ee_sample_table   -- now has trimtrailingspaces = NO
-- turn off padding
set ansi_padding off
go
alter table ee_sample_table alter column vcolumn varchar(50)  null
go
sp_help ee_sample_table   -- STILL has trimtrailingspaces = NO

Open in new window

But even with sp_help reporting 'NO' to the trim, then have a look
insert ee_sample_table values ('column   ')

select len(vcolumn), DATALENGTH(vcolumn)
from ee_sample_table

update ee_sample_table set vColumn = rtrim(vcolumn)

select len(vcolumn), DATALENGTH(vcolumn)
from ee_sample_table

Open in new window

So, you are kinda stuck with it, unless you want to recreate / create a new column.... What would you like to do?
0
Rob RudloffIT Development SpecialistAuthor Commented:
I just need what to see what TrimTrailingBlanks is set to currently for all columns in all tables.  I don't need to change the value.  
Most of our varchar fields are set to "yes", but some are set to "no", and those are the ones we are looking for.
0
Mark WillsTopic AdvisorCommented:
OK, have a look at :
select object_name(c.object_id) as table_name,c.*,t.* 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where c.is_ansi_padded = 1
and t.type = 'U'
order by 1,2,3

Open in new window

Showing a LOT more that you need, but first couple of columns show table and column names.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob RudloffIT Development SpecialistAuthor Commented:
Thanks.

The "is_ansi_padded" in your example seems to return the same value as the one I'm looking for "TrimTrailingBlanks".
As a test of that, I did the following wherein I created an "_ON" table with ANSI_PADDING ON, then another table "_OFF" with ANSI_PADDING OFF, then a third table "_BOTH" with it OFF, but added the second column after turning ANSI_PADDING ON :
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON						--- ON!	
GO

CREATE TABLE [dbo].[Table_ON](
	[SomeTextCHAR] [char](30) NULL,
	[SomeTextVARCHAR] [varchar](50) NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF					--- OFF!
GO

CREATE TABLE [dbo].[Table_OFF](
	[SomeTextCHAR] [char](30) NULL,
	[SomeTextVARCHAR] [varchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table_BOTH](
	[SomeTextCHAR] [char](30) NULL,
) ON [PRIMARY]
GO


SET ANSI_PADDING ON						--- BACK ON!
GO

ALTER TABLE dbo.Table_BOTH ADD
		[SomeTextVARCHAR] [varchar](50) NULL
GO
ALTER TABLE dbo.Table_BOTH SET (LOCK_ESCALATION = TABLE)
GO


--------
select object_name(c.object_id) as table_name,
	   c.name, 
	   c.column_id, 
	   c.is_ansi_padded 
from sys.columns as c
inner join sys.tables t on c.object_id = t.object_id
and t.type = 'U'
order by 1 DESC, 2, 3


drop table [Table_ON]
drop table [Table_OFF]
drop table [Table_BOTH] 

Open in new window

1
Mark WillsTopic AdvisorCommented:
Yes, and should have mentioned that  "is_ansi_padded" is what you are after. And a good test case above too - shows it off very well :)
0
Mark WillsTopic AdvisorCommented:
Is there anything else I can help you with ?
0
Rob RudloffIT Development SpecialistAuthor Commented:
Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.