question

lukjak avatar image
lukjak 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?
column
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for help, I modified it a little bit but it definitely does its job.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.