列出具有最大长度和最大长度的所有 SQL 列

List all SQL columns with max length AND greatest length

提问by CeejeeB

I am trying to get a list of all columns from a table with their data types, data lengths and the length of the longest value in that column.

我试图从表中获取所有列的列表，包括它们的数据类型、数据长度和该列中最长值的长度。

I have this SQL for getting the columns and their data types and lengths:

我有这个 SQL 来获取列及其数据类型和长度：

SELECT 
    Object_Name(c.object_id),
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
WHERE
    c.object_id = OBJECT_ID('MyTable')

And I have this SQL for getting the maximum length of a value:

我有这个 SQL 来获取一个值的最大长度：

SELECT Max(Len(MyColumn))
FROM MyTable

But I can't figure out how to combine them. I am using SQL Server 2008.

但我无法弄清楚如何将它们结合起来。我正在使用 SQL Server 2008。

回答by CeejeeB

Thanks for the suggestions. I have come up with the following solution. It gets me the data i need but would be interested to see if it can be made more efficient.

感谢您的建议。我想出了以下解决方案。它为我提供了我需要的数据，但有兴趣看看它是否可以提高效率。

declare @results table
(
ID varchar(36),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
Longest varchar(250),
SQLText varchar(250)
)

INSERT INTO @results(ID,TableName,ColumnName,DataType,MaxLength,Longest,SQLText)
SELECT 
    NEWID(),
    Object_Name(c.object_id),
    c.name,
    t.Name,
    case 
        when t.Name != 'varchar' Then 'NA'
        when c.max_length = -1 then 'Max' 
        else CAST(c.max_length as varchar)
    end,
    'NA',
    'SELECT Max(Len(' + c.name + ')) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id)
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
WHERE
    c.object_id = OBJECT_ID('MyTable')    


DECLARE @id varchar(36)
DECLARE @sql varchar(200)
declare @receiver table(theCount int)

DECLARE length_cursor CURSOR
    FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @receiver (theCount)
    exec(@sql)

    UPDATE @results
    SET Longest = (SELECT theCount FROM @receiver)
    WHERE ID = @id

    DELETE FROM @receiver

    FETCH NEXT FROM length_cursor
    INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor


SELECT 
    TableName, 
    ColumnName, 
    DataType, 
    MaxLength, 
    Longest 
FROM 
    @results

回答by user3761595

This is something I use to profile data that might be helpful. Just change "YOUR TABLE NAME" to your table name. It is meant to show you where columns can be trimmed.

这是我用来分析可能有用的数据的东西。只需将“您的表名”更改为您的表名。它旨在向您展示可以修剪列的位置。

DECLARE @YourTableName sysname;
DECLARE @sql nvarchar(MAX) = ''
SET @YourTableName = YOUR TABLE NAME 
CREATE TABLE #resultsTable (columnName varchar(100), columnLargestValueInData int, columnMaxLength int)

DECLARE @whileIter int = 1
DECLARE @whileTotal int  

SELECT @whileTotal = COUNT(*) FROM sys.columns c
                            INNER JOIN 
                                sys.types t ON c.user_type_id = t.user_type_id
                            WHERE
                                c.object_id = OBJECT_ID(@YourTableName)
-- print 'whileTotal: ' + CONVERT(VARCHAR,@whileTotal) -- used for testing
WHILE @whileIter <= @whileTotal
BEGIN

SELECT  @sql =  N'INSERT INTO #resultsTable (columnName,  columnLargestValueInData, columnMaxLength) SELECT ''' + sc.name + ''' AS columnName, max(len([' + sc.name + '])), ' + CONVERT(varchar,sc.max_length) + ' FROM [' + t.name + ']'  
FROM  sys.tables AS t
INNER JOIN sys.columns AS sc ON t.object_id = sc.object_id
INNER JOIN sys.types AS st ON sc.system_type_id = st.system_type_id
WHERE column_id = @whileIter
AND t.name = @YourTableName
AND st.name IN ('char', 'varchar', 'nchar', 'nvarchar')

PRINT @sql

exec sp_executesql @sql
SET @whileIter += 1
END
SELECT * FROM #resultsTable

TRUNCATE TABLE #resultsTable
DROP TABLE #resultsTable

回答by Keith Gresham

Here is a version I have used for years. It substitutes an underscore for spaces to give the true data length with trailing spaces.

这是我使用多年的版本。它用下划线代替空格以给出带有尾随空格的真实数据长度。

set nocount on;
declare @TableName varchar(150) = 'TableName';
declare @Schema varchar(20) = 'TableSchema';
declare @Columns varchar(max);
declare @Unpivot varchar(max);
declare @SQL varchar(max);

select  @Columns = STUFF((
select  ',max(len(replace([' + COLUMN_NAME + '],'' '',''_'')))[' + COLUMN_NAME + '/' 
        + isnull(ltrim(CHARACTER_MAXIMUM_LENGTH),DATA_TYPE) + ']' + CHAR(10) + CHAR(9)
from    INFORMATION_SCHEMA.COLUMNS
where   TABLE_SCHEMA = @Schema
        and TABLE_NAME = @TableName
order   by ORDINAL_POSITION
for XML PATH('')),1,1,'')

select  @Unpivot = STUFF((
select  ',[' + COLUMN_NAME + '/' + isnull(ltrim(CHARACTER_MAXIMUM_LENGTH),DATA_TYPE) + ']'
from    INFORMATION_SCHEMA.COLUMNS
where   TABLE_SCHEMA = @Schema
        and TABLE_NAME = @TableName
order   by ORDINAL_POSITION
for XML PATH('')),1,1,'')

select  @SQL = 
'select DataSize, ColumnName [ColumnName/Size]
from    (
        select ' + @Columns + 'from [' + @Schema + '].[' + @TableName + ']
        )x 
unpivot (DataSize for ColumnName in (' + @Unpivot + '))p'

print (@SQL)
exec (@SQL)

回答by Hamlet Hakobyan

   SELECT TOP 1 WITH TIES
        Object_Name(c.object_id) ObjectName,
        c.name [Column Name],
        t.Name [Data type],
        c.max_length [Max Length]
    FROM    
        sys.columns c
    INNER JOIN 
        sys.types t ON c.system_type_id = t.system_type_id
    WHERE
        c.object_id = OBJECT_ID('MyTable')
    ORDER BY c.max_length DESC

回答by user3332278

corrected above query

更正上述查询

SELECT 
    Object_Name(c.object_id),
    c.name 'Column Name',
    t.name 'Data type',
    c.max_length 'Max Length'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
WHERE
    c.object_id = OBJECT_ID('tablename')

回答by Filippo Bottega

I have extended the solution of CeejeeB adding new fields (Min, Max, NullCount and CountDinstict) and fixing a bug surrounding db names with [].
Moreover I have managed the size of unicode types.

Feel free to try the changed SQL script but pay attention because the script analyzes all user defined tablesof current DB and it may take a lot to finish...

我已经扩展了 CeejeeB 的解决方案，添加了新字段（Min、Max、NullCount 和 CountDinstict）并用 [] 修复了围绕 db 名称的错误。
此外，我管理了 unicode 类型的大小。

随意尝试更改后的 SQL 脚本，但要注意，因为该脚本会分析当前数据库的所有用户定义表，并且可能需要花费很多时间才能完成...

DECLARE @results TABLE (
    ID VARCHAR(36)
    ,TableName VARCHAR(250)
    ,ColumnName VARCHAR(250)
    ,DataType VARCHAR(250)
    ,MaxLength INT
    ,Longest INT
    ,Min VARCHAR(250)
    ,Max VARCHAR(250)
    ,NullCount BIGINT
    ,CountDistinct BIGINT
    ,SQLText VARCHAR(MAX)
    )

INSERT INTO @results (
    ID
    ,TableName
    ,ColumnName
    ,DataType
    ,MaxLength
    ,SQLText
    )
SELECT NEWID() AS ID
    ,Object_Name(c.object_id) AS TableName
    ,c.name AS ColumnName
    ,t.name AS DataType
    ,CASE 
        WHEN t.name NOT IN (
                'char'
                ,'varchar'
                ,'nchar'
                ,'nvarchar'
                ,'sysname'
                ,'text'
                )
            THEN c.max_length
        WHEN c.max_length = - 1
            THEN c.max_length
        ELSE CASE 
                WHEN t.name IN (
                        'nchar'
                        ,'nvarchar'
                        ,'sysname'
                        )
                    THEN c.max_length / 2
                ELSE c.max_length
                END
        END AS MaxLength
    ,'SELECT Max(Len(' + CASE t.name
        WHEN 'text'
            THEN 'cast('
        ELSE ''
        END + '[' + c.name + ']' + CASE t.name
        WHEN 'text'
            THEN ' as varchar(max))'
        ELSE ''
        END + ')) AS MaxLength,
        Min(' + CASE t.name
        WHEN 'bit'
            THEN 'CONVERT(int,'
        WHEN 'text'
            THEN 'cast('
        ELSE ''
        END + '[' + c.name + ']' + CASE t.name
        WHEN 'bit'
            THEN ')'
        WHEN 'text'
            THEN ' as varchar(max))'
        ELSE ''
        END + ') AS Min,
        Max(' + CASE t.name
        WHEN 'bit'
            THEN 'CONVERT(int,'
        WHEN 'text'
            THEN 'cast('
        ELSE ''
        END + '[' + c.name + ']' + CASE t.name
        WHEN 'bit'
            THEN ')'
        WHEN 'text'
            THEN ' as varchar(max))'
        ELSE ''
        END + ') AS Max, sum(case when ' + '[' + c.name + ']' + ' is null then 1 else 0 end) AS  NullCount, 
        COUNT_BIG(DISTINCT ' + CASE t.name
        WHEN 'text'
            THEN 'cast('
        ELSE ''
        END + '[' + c.name + ']' + CASE t.name
        WHEN 'text'
            THEN ' as varchar(max))'
        ELSE ''
        END + ') AS CountDistinct 
        FROM [' + OBJECT_SCHEMA_NAME(c.object_id) + '].[' + Object_Name(c.object_id) + ']' AS SQLText
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.type = 'U'

DECLARE @id VARCHAR(36)
DECLARE @SQL VARCHAR(MAX)
DECLARE @receiver TABLE (
    Longest INT
    ,Min VARCHAR(250)
    ,Max VARCHAR(250)
    ,NullCount BIGINT
    ,CountDistinct BIGINT
    )

DECLARE length_cursor CURSOR
FOR
SELECT ID
    ,SQLText
FROM @results

OPEN length_cursor

FETCH NEXT
FROM length_cursor
INTO @id
    ,@SQL

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @receiver (
        Longest
        ,Min
        ,Max
        ,NullCount
        ,CountDistinct
        )
    EXEC (@SQL)

    UPDATE @results
    SET Longest = r.Longest
        ,Min = r.Min
        ,Max = r.Max
        ,NullCount = r.NullCount
        ,CountDistinct = r.CountDistinct
    FROM @receiver r
    WHERE ID = @id

    DELETE
    FROM @receiver

    FETCH NEXT
    FROM length_cursor
    INTO @id
        ,@SQL
END

CLOSE length_cursor

DEALLOCATE length_cursor

SELECT TableName
    ,ColumnName
    ,DataType
    ,MaxLength
    ,Longest
    ,Min
    ,Max
    ,NullCount
    ,CountDistinct
FROM @results

回答by alex

add: and t.user_type_id = 167, otherwise, you get dups for non varchars. I know there are other types, it was a quick fix for a specific table

添加：和 t.user_type_id = 167，否则，您会得到非 varchars 的重复数据。我知道还有其他类型，这是针对特定表的快速修复

after c.object_id = OBJECT_ID(@YourTableName)

在 c.object_id = OBJECT_ID(@YourTableName) 之后

回答by CameO73

Note that all queries mentioned above will report some "strange" sizes - particularly for n... types (nvarchar / nchar). This slightly modified query fixes this issue:

请注意，上面提到的所有查询都会报告一些“奇怪”的大小 - 特别是对于 n... 类型（nvarchar / nchar）。这个稍微修改的查询解决了这个问题：

DECLARE @tableName AS NVARCHAR(200) = 'Items'

SELECT 
    Object_Name(c.object_id) AS 'Table',
    c.name AS 'Column Name',
    t.name AS 'Data type',
    CASE WHEN t.name LIKE 'n%' THEN c.max_length / 2 ELSE c.max_length END AS 'Max Length'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
WHERE
    c.object_id = OBJECT_ID(@tableName)

回答by Gordon Linoff

The answer is quite complicated. You need to use dynamic SQL to put together the query or do the work in Excel. You need to combine the meta data from the system tables (I would use Information_Schema.Columns) along with data from the table itself.

答案相当复杂。您需要使用动态 SQL 来组合查询或在 Excel 中完成工作。您需要将系统表中的元数据（我将使用 Information_Schema.Columns）与表本身的数据结合起来。

How to do this is explained on pages 84-90 of my book Data Analysis Using SQL and Excel. The answer is too long for this site.

我的书使用 SQL 和 Excel 进行数据分析的第 84-90 页解释了如何做到这一点。这个网站的答案太长了。

回答by Prashant Rai

We can use below query for finding column name, data type, column length:

我们可以使用以下查询来查找列名、数据类型、列长度：

SELECT owner, 
       column_name, 
       data_type, 
       data_length 
FROM   all_tab_columns 
WHERE  table_name = 'write your table name here' 
       AND owner = 'write your column name here' 
ORDER  BY column_id; 

above query will work both as a normal query as well as PL/SQL.

上面的查询既可以用作普通查询，也可以用作 PL/SQL。