CODEWITHSUNDEEP
sql-servert-sql
4 Leave Cover
4 Leave Cover

Reputation: 1276

SQL Server UPPERCASE for all data for all columns in the table

I would like to know how to convert all data to UPPERCASE for all columns name and the values in them for a table. The data may contain int but it will ignore it. Something I did was to manually update column 1 by 1 which is time consuming.

Is there any statement like:

UPDATE cust SET cust.* = UPPER(cust.*)

Thank you~

Upvotes: 1

Views: 6863

Answers (2)

sgeddes
sgeddes

Reputation: 62841

Dynamic SQL is probably your only option. Something like this should would using FOR XML querying INFORMATION_SCHEMA.COLUMNS:

declare @query varchar(max)
set @query = ''

select @query =
  STUFF((
    select ';UPDATE ' + table_name + ' SET ' + column_name + ' = UPPER(' + column_name + ')'
    from INFORMATION_SCHEMA.COLUMNS
    order by table_name, column_name
    for xml path('')
        ), 1, 1, '')

execute(@query);

SQL Fiddle Demo

Upvotes: 5

John Woo
John Woo

Reputation: 263733

Unfortunately, you can't to that directly. You need to specify each column names that need to be updated. eg

UPDATE tablename
SET    col = UPPER(col)
       colN = UPPER(colN)

But, it's not the end of the world. You can still do that but with Dynamic SQL.

Upvotes: 2

Related Questions