1

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

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
  • 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? – George.Palacios Oct 25 '18 at 7:03
2
 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.

  • Yes I want to check it for all databases of a server – Chetan Sanghani Oct 25 '18 at 9:43
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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