Find lower case field names

• newbieuser

  SSCrazy Eights

  Points: 9423

  More actions

  December 15, 2011 at 9:08 am

  #1422029

  Thank you everyone. I was able to get only the character fields in LOwer case with the below SQL

  SELECT

  'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS

  WHERE TABLE_NAME = 'table1'

  and LOWER(DATA_TYPE) NOT IN ('char','nchar','varchar','nvarchar')

  and column_name = LOWER(column_name)

  collate Latin1_General_BIN

  Thanks a lot

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 15, 2011 at 4:53 pm

  #1422262

  newbieuser (12/15/2011)

  ---

  We are facing some issues on the application with the lower case fields in the database.. So we need to change them all to upper case..

  I would really appreciate if you someone could help me with this.. The below SQL returns rows for all columns for table1 but I need only the character fields that are in lower case..

  SELECT

  'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS

  WHERE TABLE_NAME = 'table1'

  and column_name = LOWER(column_name)

  and LOWER(DATA_TYPE) IN ('char','nchar','varchar','nvarchar')

  Thanks so much

  Identify only those tables that cause such a problem. Use SSMS to rename the table and make a PASTHROUGH view with all upper case names for each of those tables. No need to mess with constraints, keys, or other things that way. Then, pork chop the people that write embedded code instead of stored procedures. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)