logo标签列表

将所有的VARCHAR列更改为NVARCHAR

sql-server
7

我在我的数据库中有大约200个VARCHAR类型的列，该类型无法存储卢比符号。现在我需要将所有这些列的数据类型从VARCHAR更改为NVARCHAR。

请问有什么简便的方法可以实现此操作吗？为什么VARCHAR支持英镑符号而不支持卢比符号？我的原因是要将英镑符号更改为卢比符号。

尝试使用nvarchar，它可以存储Unicode字符。 - Arunprasanth K V
为什么无法使用INFORMATION_SCHEMA.COLUMNS获取表列名？ - mohan111
6个回答
13
您可以使用以下查询查看所有列、它们的数据类型和所属表：

SELECT column_name, data_type, table_name

SELECT 
    t.name AS table_name,
    c.name AS column_name,
    tp.name AS data_type,
    c.max_length,
    c.is_nullable
FROM sys.tables AS t
INNER JOIN sys.columns c 
    ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types tp
    ON tp.user_type_id = c.user_type_id
WHERE tp.name = 'varchar'

从上面的查询中，您希望生成一个动态sql，将所有的VARCHAR列更改为NVARCHAR。
DECLARE @sql AS VARCHAR(MAX) = ''

SELECT @sql = @sql 
    + 'ALTER TABLE ' + table_name 
    + ' ALTER COLUMN ' + column_name + ' NVARCHAR(' 
        + CASE WHEN max_length <> - 1 THEN CAST(max_length AS VARCHAR(10))  ELSE 'MAX' END + ')'
        + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE '' END
        + ';' + CHAR(10)
FROM (
    SELECT 
        t.name AS table_name,
        c.name AS column_name,
        tp.name AS data_type,
        c.max_length,
        c.is_nullable
    FROM sys.tables AS t
    INNER JOIN sys.columns c 
        ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types tp
        ON tp.user_type_id = c.user_type_id
    WHERE tp.name = 'varchar'
)t

PRINT @sql
EXEC(@sql)
方便的脚本，谢谢。有一个小调整，记住nvarchar的数据长度是（chars*2）+2（根据：https://msdn.microsoft.com/en-us/library/ms186939.aspx），所以为了防止现有数据被截断，您可能需要更改脚本使用以下内容：`CAST((max_length * 2) + 2 AS VARCHAR(10))` - Adam Plocher
6
max_length值是字段可以容纳的字符数。前面评论中提到的文章是指实际存储成本。如果您想让varchar或nvarchar容纳255个字符，长度将以相同的方式定义：分别为varchar（255）和nvarchar（255），而不是nvarchar（512）。因此，我认为所述答案是正确的，无需修改。 - Drew
当列可为空时，这会添加"NULL"。这是不必要的，因为列默认情况下是可为空的。相反，当列不可为空时，我们应该添加"NOT NULL"。请参考此链接：https://www.w3schools.com/sql/sql_notnull.asp - undefined
5
今天我遇到了这个问题，Felix的回答非常有效，但有一个例外-如果列定义了默认值。 我做了一些研究，并提出了以下脚本，它可以删除和恢复默认值。
DECLARE @sql AS VARCHAR(MAX) = ''

SELECT @sql = @sql
    + CASE WHEN default_id>0 THEN 'ALTER TABLE '+table_name+' DROP CONSTRAINT '+OBJECT_NAME(default_id) +';'+CHAR(10) ELSE '' END
    + 'ALTER TABLE ' + table_name 
    + ' ALTER COLUMN ' + column_name + ' NVARCHAR(' 
        + CASE WHEN (max_length <> - 1 AND max_length<4001) THEN CAST(max_length AS VARCHAR(10))  ELSE 'MAX' END + ')'
        + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE '' END
        + ';' + CHAR(10)
    + CASE WHEN default_id>0 THEN 'ALTER TABLE '+table_name+' ADD DEFAULT '+default_value+' FOR '+column_name +';'+CHAR(10) ELSE '' END
FROM (
    SELECT 
        t.name AS table_name,
        c.name AS column_name,
        tp.name AS data_type,
        c.max_length,
        c.default_object_id AS default_id,
        OBJECT_DEFINITION(c.default_object_id) AS default_value,
        c.is_nullable
    FROM sys.tables AS t
    INNER JOIN sys.columns c 
        ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types tp
        ON tp.user_type_id = c.user_type_id
    WHERE tp.name = 'varchar'
)t

PRINT @sql
EXEC(@sql)
在这里添加与Felix的回答相同的评论：当列可为空时，这会添加"NULL"。这是不必要的，因为列默认情况下是可为空的。相反，当列不可为空时，我们应该添加"NOT NULL"。请参考这里：https://www.w3schools.com/sql/sql_notnull.asp - undefined
1
通常在SQL Server中，VARCHAR数据类型仅允许使用ANSI字符集，但是NVARCHAR允许使用UNICODE字符集，井号符号（156-ascii编号），$符号属于ANSI字符集。

因此，VARCHAR允许这些字符。

但是当涉及到卢比符号时，它是最近发明的，因此属于UNICODE字符集，为了实现卢比符号，我们需要使用卢比字体或字形...希望您理解为什么井号属于VARCHAR而卢比符号属于NVARCHAR...

你需要编辑你的回答，ASCII 是 7 位的。英镑符号没有 ASCII 码。ASCII 只定义了 0-127 的编码，所有其他的都是扩展。请查看 http://en.wikipedia.org/wiki/Code_page_437。 - Arunprasanth K V
1
如果您需要包含模式，可以参考Felix Pamittan的答案。
DECLARE @sql AS VARCHAR(MAX) = ''

SELECT @sql = @sql 
    + 'ALTER TABLE ' + table_schema_name + '.' + table_name 
    + ' ALTER COLUMN ' + column_name + ' NVARCHAR(' 
        + CASE WHEN max_length <> - 1 THEN CAST(max_length AS VARCHAR(10))  ELSE 'MAX' END + ')'
        + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE '' END
        + ';' + CHAR(10)
FROM (
    SELECT 
        sc.name AS table_schema_name,
        t.name AS table_name,
        c.name AS column_name,
        tp.name AS data_type,
        c.max_length,
        c.is_nullable
    FROM sys.tables AS t
    INNER JOIN sys.columns c 
        ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types tp
        ON tp.user_type_id = c.user_type_id
    INNER JOIN sys.schemas sc
        ON sc.schema_id = t.schema_id
    WHERE tp.name = 'varchar'
)t

PRINT @sql
EXEC(@sql)
0

这将循环遍历所有表及其列，并生成更新查询，用于更新那些数据类型为varchar的列。

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @Length nvarchar(128)
SET  @TableName = ''

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
            SELECT @ColumnName=MIN(QUOTENAME(COLUMN_NAME)),@Length=MIN(CHARACTER_MAXIMUM_LENGTH)
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                --AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    DATA_TYPE IN ('varchar')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName


        IF @ColumnName IS NOT NULL and @Length > 0

        BEGIN

            print (' ALTER TABLE ' +@TableName+ ' ALTER COLUMN ' + @ColumnName + ' NVARCHAR('+@Length+')')

            --exec (@dSql)

        END
    END    
END
-1
SELECT 'ALTER TABLE' +QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+ 'ALTER COLUMN ' + COLUMN_NAME + ' NVARCHAR(100)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TAbleName'
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接