如何从所有列查询max(len)?

我有临时表,其中包含近200列。 我需要知道每列的最大长度,以便我可以纠正警告“'字符串或二进制数据将被截断''。 现在我不能提到所有的列

select max(len(col1)),max(len(col2))....,,, from #tableA

现在我怎样才能获得所有列的最大长度?


是的,您可以将INFORMATION_SCHEMA.COLUMNS用于任何数据库。

前缀你的数据库名称。 在你的情况下使用tempdb.INFORMATION_SCHEMA.COLUMNS

SELECT COLUMN_NAME,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION
FROM   tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE table_name like  '%tableA%'

我有这个完全相同的问题,并发现下面的代码(从网站完整复制/粘贴)为我做了诡计。 不幸的是,我不得不通过几次迭代的数据加载来计算出每一列是罪魁祸首,但最终能够解决所有问题:

/**************************************
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) = 'Your table name to query'
       ,@SchemaName VARCHAR(200) = 'dbo'

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

尝试这个 :

SELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'

请在这里找到更多信息如何从SQL Server中的表中获取列名?

链接地址: http://www.djcxy.com/p/93849.html

上一篇: How to query max(len) from all columns?

下一篇: Trying to format SQL query results