Stored Procedure to find character fields

Question

Post reply

Stored Procedure to find character fields

david.fulcher

SSC Enthusiast

Points: 108
More actions
September 6, 2009 at 7:20 pm

#96115

Hi there,
New to stored procedures, so looking for suggestions on how I would go about accomplishing the following:
User passes table name as input variable, stored procedure then uses this to find character fields in the table and runs an UPDATE statement using RTRIM on the relevant fields to remove trailing blanks.
Make sense?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

dbo.benyos SSCommitted Points: 1944 More actions · Answer 1

Yes, as long as you don't have to deal with ntext fields..

Here you go. There's also LTRIM. Drop it if you don't need it...

Becareful and use the PRINT option first before you execute it on real DB

I left datatypes as dynamic so you can play with it if you need any mods.

CREATE PROC SPACE_INVADERS

@TableNameNVARCHAR(100)

AS

DECLARE @UpdateCommandNVARCHAR(MAX)

SELECT

@UpdateCommand = COALESCE(@UpdateCommand, '') + ',[' + COLUMN_NAME + '] = RTRIM(LTRIM([' + COLUMN_NAME + ']))

'

from

INFORMATION_SCHEMA.COLUMNS

WHERE

TABLE_NAME = @TableName

AND

DATA_TYPE IN ('varchar', 'nvarchar')

SET @UpdateCommand =

'UPDATE

'

+ @TableName

+ '

SET

'

+ SUBSTRING(@UpdateCommand,2,LEN(@UpdateCommand))

PRINT @UpdateCommand

--EXECUTE(@UpdateCommand)

GO

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

david.fulcher SSC Enthusiast Points: 108 More actions · Answer 2

david.fulcher

SSC Enthusiast

Points: 108

September 6, 2009 at 8:26 pm

#1049958

Thanks for the prompt reply - much appreciated!

🙂

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 3

Nice work benyos.

I would think about changing the following from:

@TableName NVARCHAR(100)

to

@TableName NVARCHAR(128)

That will match the actual size of the column in the INFORMATION_SCHEMA.COLUMNS view.

Also you might like to have TABLE_SCHEMA to qualify the table a little more in the select and to create the UPDATE statement.

Paul White SSC Guru Points: 150467 More actions · Answer 4

Adam,

SYSNAME might be preferred over NVARCHAR(128). SYSNAME will always reflect the correct definition between versions of SQL Server (it has changed before). Unless you are planning to migrate code to another ISO compliant database system, it is generally recommended to use the system catalog views rather than INFORMATION_SCHEMA.

The original requirement in this thread is a highly unusual one (to say the least) so it's probably best that I don't comment further on it.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

dbo.benyos SSCommitted Points: 1944 More actions · Answer 5

Agree with all comments.

Notes taken.

Thanks

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 6

Paul,

Excellent, I was totally unaware of the datatype SYSNAME. That sounds like it is definitely the best way to go. But I really can't find info in BOL on this data type to well. I am using 2005 version. Is it available in 2005 or only 2008? I checked google but can't really determine what versions this is available in.

thanks for the tip and any further info on it!

Paul White SSC Guru Points: 150467 More actions · Answer 7

Sure: Using Special Data

SYSNAME has been around at least since 6.5 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 8

Paul White (9/11/2009)
Sure: Using Special Data
SYSNAME has been around at least since 6.5 😎

:-D, geeze, I have to admit I am surprised I never knew of it. (Maybe I just forgot.) They do a good job of hiding it in BOL when looking for data types. You would think they would have it linked or maybe I missed that too.

I have some code I can think of that can utilize this.

thanks