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.

• Biju jose: so you need to pick all the character columns(CHAR,VARCHAR,NCHAR,NVARCHAR) in a database and get the maximum length stored in the that, right?
• Chetan Sanghani: No, I don't want characters, I want the only length of character.
• Biju jose: Yup, LEN(column_name).

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.

• Chetan Sanghani: Yes I want to check it for all databases of a server

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

• George.Palacios: This will fail if any databases are offline - perhaps add some sensible predicates to the WHERE clause for sys.databases based on the state or state_desc columns?

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