Блог

Главная - Вопросы по программированию - Как перечислить все nvarchar во всех таблицах базы данных SQL Server с максимальной длиной каждой?

Как перечислить все nvarchar во всех таблицах базы данных SQL Server с максимальной длиной каждой?

#sql-server

#sql-server

Вопрос:

Я попробовал это:

 select 
    TABLE_NAME, COLUMN_NAME
from 
    information_schema.columns 
where 
    data_type = 'nvarchar' 
    and (character_maximum_length = -1 or character_maximum_length = 3000)
order by 
    TABLE_NAME, COLUMN_NAME

но я также хотел бы вернуть для каждого поля его максимальную используемую длину. Как я могу это сделать?

Комментарии:

1. Почему вы хотите это сделать? Это пахнет как проблема XY .

2. У вас есть 467 столбцов такой длины? Я бы предположил, что на этапе проектирования тогда были некоторые проблемы, если честно.

3. Переместите копию базы данных в среду разработки и запустите изменение столбца. Вы очень быстро узнаете, какие из них выходят из строя из-за усечения.

4. @markzzz это потому, что я пытался выяснить, в чем была реальная проблема, то есть дизайн. Это прекрасно, теперь я это знаю, но мне действительно нужно знать версию.

5. @Ларну Microsoft SQL Server 2016, 13.x

Ответ №1:

Немного динамического SQL может достичь этого. Поскольку вы работаете на SQL Server 2016, нам нужно использовать здесь старое FOR XML PATH решение, а не более новую STRING_AGG функцию:

 DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF   N'UNION ALL'   @CRLF  
                         N'SELECT N'   QUOTENAME(s.[name],'''')   N' AS SchemaName,'   @CRLF  
                         N'       N'   QUOTENAME(t.[name],'''')   N' AS TableName,'   @CRLF  
                         N'       N'   QUOTENAME(c.[name],'''')   N' AS ColumnName,'   @CRLF  
                         N'       MAX(LEN('   QUOTENAME(c.[name])   N')) AS MaxLength'   @CRLF  
                         N'FROM '   QUOTENAME(s.[name])   N'.'   QUOTENAME(t.[name])
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                       JOIN sys.columns c ON t.object_id = c.object_id
                       JOIN sys.types ct ON c.system_type_id = ct.system_type_id
                  WHERE ct.[name] = N'nvarchar'
                    AND c.max_length IN (3000,-1)
                  FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)')   N';',1,13,N'')

PRINT @SQL; --YOur debugging friend
EXEC sys.sp_executesql @SQL;