更改列名称的大小写

I have a table in my warehouse that has columns in all uppercase. It scripts out like this:

我的仓库中有一个表，它有全部大写的列。它的脚本如下：

CREATE TABLE [dbo].[Outlet](
    [OUTLET_KEY] [varchar](10) NOT NULL,
    [OUTLET] [varchar](8) NULL,
    [CITY_CODE] [varchar](4) NULL,
    [OUTLET_NAME] [varchar](25) NULL,
    [COUNTRY_CODE] [varchar](3) NULL,
    [EXTENDED_CATEGORY_CODE] [varchar](2) NULL,
    [PHONE_NUMBER] [varchar](17) NULL
) ON [PRIMARY]

I want to change the case of the columns in the table to a much more readable format

我想将表中列的大小更改为更易读的格式

Outlet_Key
Outlet_Key
Outlet
出口
City_Code
CITY_CODE
Outlet_Name
Outlet_Name
Country_Code .......
国家代码 .......

There are about 15 tables and about 200 columns. How do I change the case for all of them?

大约有15个表和大约200个列。如何更改所有这些案例？

3 个解决方案

#1

Give this a shot:

给这个镜头：

CREATE FUNCTION dbo.Get_Proper_Cased_String
(@my_string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE
        @work_string    VARCHAR(MAX),
        @final_string   VARCHAR(MAX),
        @position       SMALLINT

    SET @final_string = ''
    SET @position = CHARINDEX(' ', @my_string)

    WHILE (@position > 0)
    BEGIN
        SET @work_string = LEFT(@my_string, @position - 1)

        IF (@work_string <> '')
            SET @final_string = @final_string + ' ' + UPPER(SUBSTRING(@work_string, 1, 1)) + LOWER(SUBSTRING(@work_string, 2, LEN(@work_string) - 1))

        SET @my_string = RIGHT(@my_string, LEN(@my_string) - @position)

        SET @position = CHARINDEX(' ', @my_string)
    END

    /* changed this to LTRIM to remove trailing space that was getting converted to underscore */
    SET @final_string = LTRIM (@final_string + ' ' + UPPER(SUBSTRING(@my_string, 1, 1)) + LOWER(SUBSTRING(@my_string, 2, LEN(@my_string) - 1)))

    RETURN @final_string
END
GO

SELECT
     'EXEC sp_rename ''' + O.name + '.' + C.name + ''', ''' + REPLACE(dbo.Get_Proper_Cased_String(REPLACE(C.name, '_', ' ')), ' ', '_') + ''', ''COLUMN'''
FROM
    sys.objects O
INNER JOIN sys.columns C ON
    C.object_id = O.object_id
WHERE
    O.object_id = OBJECT_ID('<table_name>')

This code assumes that the only non-character value that you have is "_" and that you don't have successive underscores (i.e., MY____COLUMN would give the wrong results).

此代码假定您拥有的唯一非字符值是“_”，并且您没有连续的下划线（即，MY____COLUMN会给出错误的结果）。

#2

Here is an easy way to generate the sp_rename commands using just T-SQL, but this doesn't exactly match your naming definition. Getting the first letter after any underscore to be upper cased will require much more elaborate handling (e.g. 26 nested replace(name, '_a', '_A') calls), but perhaps this is a useful start:

这是一种使用T-SQL生成sp_rename命令的简单方法，但这与您的命名定义不完全匹配。获得任何下划线后面的第一个字母需要更精细的处理（例如26个嵌套替换（名称，'_a'，'_ A'）调用），但这可能是一个有用的开始：

SELECT 'EXEC sp_rename ''' 
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + '.' + QUOTENAME([name]) + ''', '
    + '''' 
    + QUOTENAME(UPPER(LEFT([name],1))+LOWER(SUBSTRING([name], 2, LEN([name])))) 
    + ''', ''COLUMN'';'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.Outlet');

Run that script in an SSMS query window, then copy the output and run it in a new window.

在SSMS查询窗口中运行该脚本，然后复制输出并在新窗口中运行它。

Okay, here is the version that does the nested REPLACE() calls. It's not pretty, but it works:

好的，这是执行嵌套REPLACE（）调用的版本。它不漂亮，但它的工作原理：

USE tempdb;
GO

SET NOCOUNT ON;
GO

CREATE TABLE dbo.Outlet
(
    [OUTLET_KEY] [varchar](10) NOT NULL,
    [OUTLET] [varchar](8) NULL,
    [CITY_CODE] [varchar](4) NULL,
    [OUTLET_NAME] [varchar](25) NULL,
    [COUNTRY_CODE] [varchar](3) NULL,
    [EXTENDED_CATEGORY_CODE] [varchar](2) NULL,
    [PHONE_NUMBER] [varchar](17) NULL,
) ON [PRIMARY];
GO

SELECT 'EXEC sp_rename '''
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + '.' + QUOTENAME([name]) + ''', '
    + '''' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             QUOTENAME(UPPER(LEFT([name],1))+LOWER(SUBSTRING([name], 2, LEN([name]))))
             , '_a', '_A'), '_b', '_B'), '_c', '_C'), '_d', '_D'), '_e', '_E')
             , '_f', '_F'), '_g', '_G'), '_h', '_H'), '_i', '_I'), '_j', '_J')
             , '_k', '_K'), '_l', '_L'), '_m', '_M'), '_n', '_N'), '_o', '_O')
             , '_p', '_P'), '_q', '_Q'), '_r', '_R'), '_s', '_S'), '_t', '_T')
             , '_u', '_U'), '_v', '_V'), '_w', '_W'), '_x', '_X'), '_y', '_Y'), '_z', '_Z')
    + ''',' + '''COLUMN'';'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.Outlet');

GO
DROP TABLE dbo.Outlet;

#3

I would write a small batch program using smo to enumerate all tables and tablecolumns and change the name of the columns.

我会使用smo编写一个小批处理程序来枚举所有表和tablecolumns并更改列的名称。

#1