List all SQL columns with max length AND greatest

2019-04-05 02:53发布

站内文章 / 移动开发
13 0 0

问题:

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 cant figure out how to combine them.

I am using MSSQL 2008.

回答1:

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


回答2:

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


回答3:

   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


回答4:

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


回答5:

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)


回答6:



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


回答7:

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)



回答8:

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)


回答9:

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.



回答10:

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


标签： sql sql-server-2008 tsql
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~