asked
Getting maximum length of data in all columns
Hi! I have this problem, I've been trying to figure it out for days now, and I cannot find right solution. So to problem is this - we load database with values retrieved from xml forms. We dont have xsd schemas, and we dont have any useful information about maximum data length of xml fields, that we are taking data from. It causes problems f.e. we assumed that this field won't go over 512 characters, and lets say 3 million rows pass, and the next one has 526 characters in this field, and our process raises errors. So we just made every column at max length, then we obtain the data and we want to get the maximum length of data in every column for future work. I've been trying max(len(COLUMN_NAME)) function, and it does its job - it gets the maximum length of data in column(not the length of column name etc). But I cant find a way to use this function for every column within a table in one query. We have many tables, every table has a lot of diffrent columns and its basically impossible to manually surround every column name with max(len(...)). I've been trying sp_MSForEachTable also, but I cant figure out how to use it so it execute query for all columns. Is there any way to do this in one query?
It sounds like you have the loop for the tables figured out and need a nudge with the columns. Using the elementData table in this recent question as an example, [
https://ask.sqlservercentral.com/questions/138290/what-is-wrong-with-this-table.html][1], I think you could use something like this to generate the columns query for each table: declare @sql nvarchar(max) = N''; select @sql = @sql + 'max(len([' + name + '])) as [' + name + '_max_len], ' from sys.columns where object_id = object_id('elementdata') select @sql = 'SELECT ' + @sql + '''elementdata'' as table_name FROM [elementdata]' print @sql exec (@sql); It generates and executes this query to return the max length for each column: SELECT max(len([name])) as [name_max_len], max(len([symbol])) as [symbol_max_len], ... ... max(len([shell7])) as [shell7_max_len], max(len([funFact])) as [funFact_max_len], 'elementdata' as table_name FROM [elementdata] I've hard-coded the table name. You can just inject the table name from your loop. [1]:
https://ask.sqlservercentral.com/questions/138290/what-is-wrong-with-this-table.html
17 People are following this question.
