Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: simple db modification question

    Is there a quick way to convert all table and column names in MSSQL7 from upper case to lower case?

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Run this, this will create the script for you, haven't tested it but should work

    select 'exec sp_rename ',''''+table_name+''''+ ','+''''+ upper(table_name)+''''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Actually run this, that way you will only get the distinct table names

    select distinct'exec sp_rename ',''''+table_name+''''+ ','+''''+ upper(table_name)+''''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Mar 2004
    Posts
    2
    Thanks, Ray. The statement doesn't work quite as expected. Would you mind explaining the syntax and what it does. Then I'll see if I can tweak it. I appreciate the help.

    Originally posted by rhigdon
    Actually run this, that way you will only get the distinct table names

    select distinct'exec sp_rename ',''''+table_name+''''+ ','+''''+ upper(table_name)+''''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

  5. #5
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    When you run it, it should create a set of sp_rename scripts that you can then copy into QA and run to rename the tables, they would need to be owned by DBO or your login though.

    TO read more about info schema views, go here:

    http://www.sqlservercentral.com/colu...chemaviews.asp
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    WARNING: This code will force all table and column names to lower case. If the case means anything in your database, you will loose information!!!

    Ray's script actually produces another script which would set the column names to upper case if you ran it. The following code uses cursors (I know, folks here just love cursors) to allow a Transact-SQL script to do what you want with no user intervention.
    PHP Code:
    --  ptp  20040313  Rename all tables and columns to lower case

    DECLARE @
    cmd        NVARCHAR(4000)

    DECLARE 
    zTable CURSOR FOR SELECT
       
    'EXECUTE sp_rename ''[' TABLE_SCHEMA
    +  '].[' TABLE_NAME ']'', ''[' 
    +  TABLE_SCHEMA '].[' 
    +  Lower(TABLE_NAME) + ']'''
       
    FROM INFORMATION_SCHEMA.TABLES
       WHERE  TABLE_NAME COLLATE Latin1_General_BIN LIKE 
    '%[A-Z]%'

    OPEN zTable
    FETCH zTable INTO 
    @cmd

    WHILE = @@fetch_status
       BEGIN
          EXECUTE 
    (@cmd)
          
    FETCH zTable INTO @cmd
       END

    CLOSE zTable
    DEALLOCATE zTable

    DECLARE zColumn CURSOR FOR SELECT
       
    'EXECUTE sp_rename ''[' TABLE_SCHEMA
    +  '].[' TABLE_NAME '].[' COLUMN_NAME
    +  ']'', ''[' TABLE_SCHEMA '].[' TABLE_NAME '].['
    +  Lower(COLUMN_NAME) + ']'''
       
    FROM INFORMATION_SCHEMA.COLUMNS
       WHERE  COLUMN_NAME COLLATE Latin1_General_BIN LIKE 
    '%[A-Z]%'

    OPEN zColumn
    FETCH zColumn INTO 
    @cmd

    WHILE = @@fetch_status
       BEGIN
          EXECUTE 
    (@cmd)
          
    FETCH zColumn INTO @cmd
       END

    CLOSE zColumn
    DEALLOCATE zColumn 
    Please think twice, and use this only after you have a good backup!!! Case means something in my datatbases, and I can't think of any easy way to reverse this process.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •