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.

  • 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?
    – Biju jose
    Commented Oct 25, 2018 at 6:51
  • No, I don't want characters, I want the only length of character. Commented Oct 25, 2018 at 6:55
  • Yup, LEN(column_name).
    – Biju jose
    Commented Oct 25, 2018 at 6:56

3 Answers 3

       TABLE_NAME ,
       COLUMN_NAME ,
        DATA_TYPE ,
       NULL AS stored_len 
   INTO  #temp  
WHERE DATA_TYPE IN('varchar','char','nchar','nvarchar')

'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.

  • Yes I want to check it for all databases of a server Commented Oct 25, 2018 at 9:43

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

set @cmd ='
use '+@dbname+'
select max(len(column1 )) from table1 
fetch next from c into @dbname
close c
deallocate c
  • 1
    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? Commented Oct 25, 2018 at 7:03

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

    [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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.