CODEWITHSUNDEEP
sql-servert-sql
Eric Ness
Eric Ness

Reputation: 10417

Query SQL Server System Tables For Character Data Type Sizes

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.

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.*

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.

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

Upvotes: 2

Views: 4441

Answers (4)

Chicago_David
Chicago_David

Reputation: 11

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

Upvotes: 1

Narnian
Narnian

Reputation: 3908

Updated answer...

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

Upvotes: 1

EricZ
EricZ

Reputation: 6205

SELECT  *
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH > 450

Upvotes: 1

JNK
JNK

Reputation: 65157

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.

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

Upvotes: 1

Related Questions