Find max content length of column of particular table from entire SQL server

Question

I have a 3 SQL server: Server1,Server2 and Server3.

Each search has approx 50-60 databases.

In a particular database there is a table name table1 and it has a column named column1 with datatype varchar(max).

Now I want to change it's datatype to Nvarchar(500) but I can't as I dont know how much data consist in that column, If I blindly update it's datatype then it may happens that some of the data may be lossed.

So what I want is,I want to prepare a query to server level which gives me max content length of that column from entire server So I can get an idea about what is the max content length of that column.

So Based on that I can get max length and according to that max length,I can planned to alter that column.

I will be very grateful to you guys if someone can help me to prepare this query.

Answer 1

 SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
        DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH,
       NULL AS stored_len 
   INTO  #temp  
   FROM   INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN('varchar','char','nchar','nvarchar')

SELECT 
'UPDATE #temp
SET stored_len =(SELECT MAX(LEN('+column_name+')) FROM '+table_name+')
WHERE table_name='''+table_name+'''
AND COLUMN_NAME ='''+column_name+''''
FROM #temp

Run this in your database context and execute the final select statement generated, this provide you the list of table and the max lenght of characters stored in it in the #temp table.

part of the answer is copied from the this snippet

Edit:1 I think your question is to run this in all database in a server. I will modify my solution according to that.

Answer 2

To do it for each database on a server you could do this:

To do it on the 3 servers, either run it on each, or create a central management server.

declare @cmd nvarchar(max)

declare @dbname nvarchar(500)
declare c cursor for select name from sys.databases where database_id > 4 and state_desc = 'ONLINE'
open c
fetch next from c into @dbname
while @@FETCH_STATUS = 0

begin
set @cmd ='
use '+@dbname+'
select max(len(column1 )) from table1 
'
--print(@cmd)
exec(@cmd)
fetch next from c into @dbname
end
close c
deallocate c

Answer 3

I have tried below query and gives me result as I want.

CREATE TABLE #tmp(
    [dbname] [nvarchar](128) NULL,
    [character_maximum_length] [nvarchar](max) NULL
    ) 

    insert into #tmp
    exec sp_msforeachdb 'use ? select  db_name(),''select max(len(appPageDesc))  from ''+db_name()+''.dbo.tblPages'' from information_schema.columns'

    select distinct * from #tmp

Thanks all for your help