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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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:
@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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.