查询SQL Server系统表的字符数据类型大小

We're changing the data type of one of the columns in our SQL Server 2008 database from varchar(900) to nvarchar(900). This will cause the data size of the column to change from 900 bytes to 1800 bytes.

我们正在将SQL Server 2008数据库中的一个列的数据类型从varchar(900)更改为nvarchar(900)。这将导致列的数据大小从900字节更改为1800字节。

This column is included in an index and when we make the change to the index we get the warning

该列包含在索引中，当我们对索引进行更改时，我们将得到警告

*Warning! The maximum key length is 900 bytes. The index 'INX_SomeTable' has maximum length of 1800 bytes. For some combination of large values, the insert/update operation will fail.*

*警告!最大密钥长度为900字节。索引“INX_SomeTable”的最大长度为1800字节。对于一些大值的组合，插入/更新操作将失败

We're planning to address this by reducing the field size from nvarchar(900) to nvarchar(450). I'd like to make sure that there are no columns feeding into the field that exceed 450 characters.

我们计划通过减少nvarchar(900)到nvarchar(450)的字段大小来解决这个问题。我想确保没有列输入超过450个字符的字段。

How can I query the system tables or DMVs to find all character fields (varchar or nvarchar) that exceed a certain size?

如何查询系统表或DMVs以查找超过一定大小的所有字符字段(varchar或nvarchar) ?

4 个解决方案

#1

SELECT  *
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH > 450

#2

select OBJECT_NAME(object_id) as 'TableorView', name
from DB.sys.columns
WHERE system_type_id IN (167, 231, 175, 239)
AND max_length > 450

I included system types for CHAR and NCHAR as well just in case.

我还包括了CHAR和NCHAR的系统类型，以防万一。

If you want additional types you can look in sys.types for a list.

如果您需要其他类型，可以在sys中查找。类型的一个列表。

#3

Updated answer...

更新的答案……

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNS.DATA_TYPE IN ('nvarchar', 'varchar', 'char', 'nchar')
    AND CHARACTER_MAXIMUM_LENGTH > 450

#4

Here is a result set that includes schema and limits to just the types you want to retrieve.

这里有一个结果集，它包含模式和仅限制您想要检索的类型。

SELECT 
            sch.name as 'Schema'
,           OBJECT_NAME(col.object_id) as 'TableorView'
,           col.name  as 'Column'
,           typ.name + ' (' + convert(varchar,col.max_length) + ')' as 'Type'
FROM        sys.objects AS o WITH (NOLOCK)
INNER JOIN  sys.schemas sch WITH (NOLOCK) 
ON          o.schema_id = sch.schema_id 
INNER JOIN  sys.columns col
ON          o.object_id = col.object_id
INNER JOIN  sys.types typ
ON          col.system_type_id = typ.system_type_id
WHERE       col.system_type_id IN (
                SELECT system_type_id 
                FROM sys.types
                WHERE name IN ('CHAR', 'NCHAR')     --Add Types you want here
                )
AND         sch.name NOT IN ('sys')                 --Add schemas you don't want here
ORDER BY 1,2,3

#1

SELECT  *
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH > 450

#2

select OBJECT_NAME(object_id) as 'TableorView', name
from DB.sys.columns
WHERE system_type_id IN (167, 231, 175, 239)
AND max_length > 450