2

This week the risk unit told us that our system must only accept texts in uppercase. We are on production for almost 6 months and this wasn't contemplated on the initial project. Is there any easy way to transform all varchars of the database to uppercase?

F. Penteado
  • 57
  • 1
  • 9
  • [Changing SQL Server collation to case insensitive from case sensitive?](https://stackoverflow.com/questions/3296428/changing-sql-server-collation-to-case-insensitive-from-case-sensitive#3296444) – Lukasz Szozda Oct 08 '18 at 14:24
  • How do you define "easy"? – MJH Oct 08 '18 at 14:24
  • No, what I really need is like a css' 'text-transform: uppercase' to the whole database's columns/rows/fields. – F. Penteado Oct 08 '18 at 14:27
  • The "easy" would be something that I don't need to do 'UPDATE table SET column = UPPER(column)' for every table and column – F. Penteado Oct 08 '18 at 14:28
  • You can't just magically change the case of your data. To update data requires an update statement. – Sean Lange Oct 08 '18 at 14:37
  • 2
    What is so hard about generating update statements for every table/column which is a varchar? Write a query that generates your SQL using `INFORMATION_SCHEMA.COLUMNS` or `sys.columns` then just run it. – MJH Oct 08 '18 at 14:39
  • The problem is that it is an ERP, there is about 150 tables, but ok, I will do it on "brutal force". Thank you so much. – F. Penteado Oct 08 '18 at 14:43

1 Answers1

5

I don't know if this is the best possible approach, but I use it sometimes. It uses sys.columns catalog view, generates dynamic statement using FOR XML clause and executes generated statement with sp_executesql.

DECLARE 
    @stm nvarchar(max),
    @err int

SELECT @stm = (
    SELECT CONCAT(
        'UPDATE ', OBJECT_NAME(c.OBJECT_ID), 
        ' SET ',
        c.name,
        ' = UPPER(',
        c.name, '); ')
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id = t.user_type_id
    WHERE t.name = 'varchar'
    ORDER BY c.OBJECT_ID
    FOR XML PATH('')
)
EXEC @err = sp_executesql @stm
IF @err = 0 
    PRINT 'OK'
ELSE 
    PRINT 'Error'
Zhorov
  • 28,486
  • 6
  • 27
  • 52