Sql Server: Query to return length of longest current value in all variable length datatypes columns in all tables

I need a T-SQL statement that will return the longest current value of each column in all tables in a schema that have a text or number datatype.. dates, etc. can be ignored.  With Numbers, precision/scale does not matter, just the net total length of the data.
No need to return the actual data.

Desired Results:
Table Name           Column Name  datatype      longest current value
    T1                                  C1              nvchar                          26
    T1                                  C4            number                          14
    T2                                  C1            number                            5


Any and all pointers & suggestions appreciated..
Jblue RAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
@Author - Here is the complete solution for your.

DATA GENERATION.
CREATE TABLE testMaxLength1
(
	 ID INT
	,Vals VARCHAR(100)
)
GO


INSERT INTO testMaxLength1 VALUES
(4, 'Hello World'),
(42, 'Hello World1')
GO


CREATE TABLE testMaxLength2
(
	 ID INT
	,Vals VARCHAR(100)
	,Flats DECIMAL(30,8)
)
GO


INSERT INTO testMaxLength2 VALUES
(4, 'Pawan World Hello' , 12323.9877 ),
(2, 'Pawa' , 23.1 )
GO

Open in new window


SOLUTION
IF OBJECT_ID('tempdb..#TempTableNames') IS NOT NULL
DROP TABLE #TempTableNames

IF OBJECT_ID('tempdb..#FinalData') IS NOT NULL
DROP TABLE #FinalData

CREATE TABLE #FinalData ( TableName VARCHAR(MAX), ColumnName VARCHAR(MAX), DataType VARCHAR(MAX), SetLength BIGINT, MaxLength BIGINT)
DECLARE @SQL VARCHAR(MAX)=''
DECLARE @TableName AS VARCHAR(250) = ''
SELECT name INTO #TempTableNames FROM sys.tables WHERE type = 'U'

WHILE EXISTS ( SELECT TOP 1 1 FROM #TempTableNames )
BEGIN

	SELECT TOP 1 @TableName = name FROM #TempTableNames

	SELECT @SQL = @SQL + 'SELECT ' + '''' + @TableName + '''' + ' as TableName' + ',' + QUOTENAME(sc.name, '''') + ' AS ColumnName, ' + QUOTENAME(t.name, '''') + ' AS DataType, ' +
	QUOTENAME(sc.max_length, '''') + ' AS SetLength, MAX(LEN(' + QUOTENAME(sc.name) + ')) AS MaxLength FROM '+@TableName+ char(10) +' UNION '
	FROM sys.columns sc
	INNER JOIN sys.types t on t.system_type_id = sc.system_type_id and t.name != 'sysname'
	WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)
	SET @SQL = LEFT(@SQL, LEN(@SQL)-6)

    INSERT INTO #FinalData	
	EXEC(@SQL)

	DELETE FROM #TempTableNames WHERE name = @TableName

END

SELECT * FROM #FinalData

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
TableName                     ColumnName       DataType          SetLength            MaxLength
----------------------------- ---------------- ----------------- -------------------- --------------------
testMaxLength1                ID               int               4                    2
testMaxLength1                Vals             varchar           100                  12
testMaxLength1                ID               int               4                    2
testMaxLength1                Vals             varchar           100                  12
testMaxLength2                Flats            decimal           17                   14
testMaxLength2                ID               int               4                    1
testMaxLength2                Vals             varchar           100                  17

(7 row(s) affected)

Open in new window

0
 
Scott PletcherSenior DBACommented:
Not sure specifically what you want on "number" columns.  

If the largest value in a decimal column is 1234.56, would the length be 4, 6 or 7?
If the largest value in an int column is 23,476,281, do you want to see a length of 8?  Or just ignore ints (tinyint, smallint, etc.)?
0
 
Jblue RAuthor Commented:
Thank you for your response.................

If the largest value in a decimal column is 1234.56, would the length be 4, 6 or 7?        =   7
 If the largest value in an int column is 23,476,281, do you want to see a length of 8?   = 10 -- characters used
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Pawan KumarDatabase ExpertCommented:
Please use this script-

DECLARE @SQL VARCHAR(MAX)=''

DECLARE @TableName sysname

SET @TableName = 'A'

SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT ' + QUOTENAME(sc.name, '''') + ' AS ColumnName, ' + QUOTENAME(t.name, '''') + ' AS DataType, ' +

QUOTENAME(sc.max_length, '''') + ' AS SetLength, MAX(DATALENGTH(' + QUOTENAME(sc.name) + ')) AS MaxLength FROM '+@TableName+ char(10) +' UNION '

FROM sys.columns sc

join sys.types t on t.system_type_id = sc.system_type_id and t.name != 'sysname'

WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)

SET @SQL = LEFT(@SQL, LEN(@SQL)-6)

PRINT @SQL

EXEC(@SQL)

Open in new window


OUTPUT
/*------------------------
OUTPUT
ColumnName DataType SetLength MaxLength
---------- -------- --------- -----------
Id         int      4         4
Val        varchar  100       5

(2 row(s) affected)

Open in new window


From MS-
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/adb3c27c-d7b8-409a-ad05-81360e9d062b/get-max-length-of-every-column-of-a-table-in-one-query?forum=transactsql
0
 
Pawan KumarDatabase ExpertCommented:
Sample testing for you.

CREATE TABLE testMaxLength
(
	 ID INT
	,Vals VARCHAR(100)
)
GO


INSERT INTO testMaxLength VALUES
(4, 'Hello World'),
(42, 'Hello World1')
GO

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
ColumnName DataType SetLength MaxLength
---------- -------- --------- -----------
ID         int      4         4
Vals       varchar  100       12

(2 row(s) affected)

Open in new window

0
 
Ryan ChongCommented:
If the largest value in an int column is 23,476,281, do you want to see a length of 8?   = 10 -- characters used
if this is an int column, it should returns char length = 8
0
 
Pawan KumarDatabase ExpertCommented:
@Ryan

You cannot insert 23,476,281 in an INT Column. The value has to be 23476281
0
 
Ryan ChongCommented:
You cannot insert 23,476,281 in an INT Column. The value has to be 23476281
Exactly Pawan, so that's what I mean is pretty simple:

declare @myVarText varchar(20);
declare @myVar int;

set @myVarText = '23,476,281';

set @myVar = cast(cast(@myVarText as money) as int)

select
@myVarText myVarText, len(@myVarText) len_myVarText,
@myVar myVar, len(@myVar) len_myVar

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@Ryan-
We do not have to do any conversion. For each column there will a single data type. So in the column what is the maximum length of data needs to returned. Please see my last comment. Thanks
0
 
Ryan ChongCommented:
We do not have to do any conversion. For each column there will a single data type
well, that's for illustration of value: '23,476,281' which in reality, it cannot be stored as a value in an int column.

'23,476,281' and 23476281 are not exactly the same.
0
 
Jblue RAuthor Commented:
Thank you for a great solution!!

""If the largest value in an int column is 23,476,281, do you want to see a length of 8?  Or just ignore ints (tinyint, smallint, etc.)? ""

the above came from a comment/question.  Had nothing to do with original post nor query behind the post.

Thank you !!
0
 
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help as always.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.