July 31, 2017 at 7:13 am
_watching - Sunday, July 30, 2017 10:39 PMFor anyone else who stumbles across this thread looking for answers, this blog post details the code to perform similar functionality.
Why would you want to use the code posted on that blog? It is using a while loop which is horribly inefficient. The code I posted is set based and doesn't need any looping.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2025 at 10:09 am
For a loopless solution that get all lengths in a single query:
DECLARE @SourceTableName sysname = 'MyTableName'
DECLARE @SQL nvarchar(max) = ''
SELECT @SQL = CONCAT('
SELECT ColName,ColLen
FROM
(
SELECT <<colsagg>> FROM ',@SourceTableName,'
)c
UNPIVOT
(
ColLen FOR ColName IN
(<<cols>>)
) AS upvt
'
)
DECLARE @cols NVARCHAR(MAX) = ''
DECLARE @colsagg NVARCHAR(MAX) = ''
SELECT
@cols = string_agg(quotename(c.name),',')
,@colsagg = string_agg(CONCAT('max(len(',c.name,')) as ',c.name) ,',')
FROM sys.tables t join sys.columns c on t.object_id = c.object_id and t.name = @SourceTableName
SELECT @SQL = REPLACE(@SQL,'<<colsagg>>',@colsagg)
SELECT @SQL = REPLACE(@SQL,'<<cols>>',@cols)
EXECUTE sp_executesql @SQL
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply