SQL to get max length of values in every table column
I had an issue to resolve once upon a time where data could not be loaded into a table because “string or binary data would be truncated”.
The target table had well over 50 columns with several million rows.
The only easy way to figure out what incoming data was longer than the specified schema was to implement the following SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
/************************************** 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 |
