Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Find Length of longest value from columns in all tables from a database

In this puzzle we have to find Length of longest value from columns in all tables from a database.

Sample Input

TABLE : testMaxLength1

ID Vals
4 Hello World
42 Hello World1

TABLE : testMaxLength2

ID Vals Flats
4 Pawan World Hello 12323.98770000
2 Pawa 23.10000000

Expected Output

TableName ColumnName DataType SetLength MaxLength
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

Script

Use below script to create table and insert sample data into it.

--

/* CREATE a new DATABASE called of your choice and execute below tables*/

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

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--

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 DISTINCT * FROM #FinalData

--

Output

--

/*------------------------
OUTPUT
------------------------*/
TableName                     ColumnName   DataType      SetLength            MaxLength
----------------------------- ------------ ------------- -------------------- --------------------
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

(5 row(s) affected)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements