CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
CeejeeB
CeejeeB

Reputation: 3114

List all SQL columns with max length AND greatest length

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:

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:

SELECT Max(Len(MyColumn))
FROM MyTable

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

Upvotes: 25

Views: 95567

Answers (14)

Hrvoje
Hrvoje

Reputation: 15182

Tried most of the solutions, but only this one worked fine for me. All thanks goes to author in description.

Just change TableName and SchemaName in first 2 lines to your table name and to scheme where your table is. Make sure your are executing over right database or just put

Use 
yourDatabse
go

at the beginning of this script.

/**************************************
SQL to get max length of values in every table column.

This SQL queries the specified table to get the max length
of all the values in every column.

To work, load all the incoming data into a "permanent" temporary table
where every column is defined as a varchar(max).

Then run this script against that "permanent" temporary table, comparing
the results against the original table's schema to see what column, and
then what record(s), is/are causing the issue.

Example, if the max length of a value in one of the columns is 270,
but your original table's schema is varchar(255), obviously either
the original table's schema will have to be altered or the data corrected.

Code from: http://cc.davelozinski.com
**************************************/

--The table we'll be performing this query on to get the lengths of every column.
--Default is dbo schema. Change as appropriate for your table.
DECLARE @TableName VARCHAR(200) = 'TableName'
       ,@SchemaName VARCHAR(200) = 'SchemaName'

DECLARE @MaxLengthDefault INT
       ,@Column VARCHAR(50)
       ,@MaxLength INT
       ,@MaxLengthString VARCHAR(10)
       ,@ColumnID INT
       ,@MaxColumnID INT
       ,@Command VARCHAR(2000)

CREATE TABLE #Temp (
       column_name VARCHAR(50)
       ,max_length INT
       ,max_length_default INT
)

SELECT @ColumnID = min(b.[column_id])
       ,@MaxColumnID = max(b.[column_id])
FROM sys.tables a
INNER JOIN sys.columns b on a.[object_id] = b.[object_id]
WHERE a.[name] = @TableName
       and SCHEMA_NAME(a.[schema_id]) = @SchemaName

--SELECT @ColumnID, @MaxColumnID

WHILE(@ColumnID <= @MaxColumnID)
BEGIN
    SET @Column = null

    SELECT @Column = b.[name]
              ,@MaxLengthDefault = b.[max_length]
       FROM sys.tables a
       INNER JOIN sys.columns b on a.[object_id] = b.[object_id]
       WHERE a.[name] = @TableName
              and SCHEMA_NAME(a.[schema_id]) = @SchemaName
              and b.[column_id] = @ColumnID

    --SELECT @Column, @MaxLengthDefault

    IF ( @Column is not null )
    BEGIN
              SET @Command = 'INSERT INTO #Temp(column_name, max_length, max_length_default)
                SELECT ''' + @Column + '''
                ,MAX(LEN(CAST([' + @Column + '] as VARCHAR(8000))))
                ,' + CAST(@MaxLengthDefault as VARCHAR(5)) +
                ' FROM [' + @SchemaName + '].[' + @TableName + ']
                WHERE [' + @Column + '] IS NOT NULL'
              --SELECT @Command
              EXEC(@Command)
       END

    SET @ColumnID = @ColumnID + 1
END

SELECT * FROM #Temp

DROP TABLE #Temp

Upvotes: 0

Pruthviraj Vachhani
Pruthviraj Vachhani

Reputation: 97

Comparing TableA columns with TableB columns by size is not enough. You have to get max data length of each columns data from tableA and than compare it with the TableB column size to find the culprit column.

Upvotes: 0

Lorenzo Yniguez
Lorenzo Yniguez

Reputation: 9

Finally figured this out to find the max datalength and max_length for each column in a table

SELECT name, max_length, MAX(DATALENGTH(name))
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID('table name')
GROUP BY name, max_length

Upvotes: 0

Prashant Rai
Prashant Rai

Reputation: 33

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.

Upvotes: -2

Filippo Bottega
Filippo Bottega

Reputation: 109



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 tables of current DB and it may take a lot to finish...

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

Upvotes: 3

CameO73
CameO73

Reputation: 103

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

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)

Upvotes: 0

Keith Gresham
Keith Gresham

Reputation: 153

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)

Upvotes: 7

alex
alex

Reputation: 1

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

after c.object_id = OBJECT_ID(@YourTableName)

Upvotes: 0

user3909732
user3909732

Reputation: 23

Slightly amended but works a treat.

SELECT 
    Object_Name(c.object_id),
    c.name 'Column Name',
    t.name 'Data type',
    c.max_length 'Max Length',
    MAX(LEN(C.NAME))
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
WHERE
    c.object_id = OBJECT_ID('<table name>')
GROUP BY 
    Object_Name(c.object_id),
    c.name ,
    t.name ,
    c.max_length

Upvotes: -3

user3761595
user3761595

Reputation: 201

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

Upvotes: 19

user3332278
user3332278

Reputation: 21

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')

Upvotes: 2

CeejeeB
CeejeeB

Reputation: 3114

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

Upvotes: 16

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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.

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.

Upvotes: -2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

   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

Upvotes: 3

Related Questions