--

/* 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

--

--

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

--