Styjun

I am having trouble to get the max length of records in one column in all tables.

I would only like to display the max length for each table for the specific column.

Below is what I have tried, I already found the way to return the column I need, but now, i need to get the max len. I know this is not the right way.

select max(len(site)) as site from 
(
SELECT t.name AS TableName 
FROM sys.columns c
 JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE 'site%')A

The expected result will display the column name, the table name and also the max length of the records for that column.

Thanks in advance

I don't understand what are you really trying to do, but I think you want something like

CREATE TABLE T1(
 Site1 VARCHAR(45)
);
CREATE TABLE T2(
 Site2 VARCHAR(45)
);

INSERT INTO T1 VALUES ('A'), ('AA');
INSERT INTO T2 VALUES ('BBB'), ('BBBBB');

DECLARE @SQL NVARCHAR(MAX) = 'SELECT ';

SELECT @SQL = @SQL + 
 N'(SELECT MAX(LEN(' + --You can also add ISNULL([Col],0) to get 0
 QUOTENAME(c.name) + ')) FROM '+ 
 QUOTENAME(t.name) + ') AS ' + 
 QUOTENAME(t.name + '.'+c.name) + ', '
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE 'site%';

SET @SQL = LEFT(@SQL, LEN(@SQL)-1);

EXEC sp_executesql @SQL;

Which will returns:

+----------+----------+
| T1.Site1 | T2.Site2 |
+----------+----------+
| 2 | 5 |
+----------+----------+

Live Demo

Try this:You will get individual Scripts to execute.

select 'select Max(len('+COLUMN_NAME+')),'''+COLUMN_NAME+ ''' as ColumnName ,'''+TABLE_NAME+''' as TableName from ' +table_name 
from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'YourColumnName'

If I understand your question, you may try to generate a dynamic SQL statement and execute this statement:

-- Declarations
DECLARE @stm nvarchar(max)
SET @stm = N''

-- Dynamic SQL
SELECT @stm = (
 SELECT CONCAT(
 N'UNION ALL ',
 N'SELECT ''',
 t.name,
 N''' AS TableName, ''',
 c.name,
 N''' AS ColumnName, ',
 N'ValueLength = (SELECT MAX(LEN(',
 QUOTENAME(c.name),
 ')) FROM ',
 QUOTENAME(t.name),
 N')'
 )
 FROM sys.columns c
 JOIN sys.tables t ON c.object_id = t.object_id
 WHERE c.name LIKE 'site%'
 ORDER BY t.name, c.name
 FOR XML PATH('')
)
SET @stm = STUFF(@stm, 1, 10, N'')

-- Execution
PRINT @stm
EXEC sp_executesql @stm