Dynamic T-SQL - Alter column definition to max length of field

Question

I am trying to dynamically create code to alter the column definition to the max length of the field.

Note that the contents of the database will not change.

Here is what I have so far, but I cannot separately execute the max length query to get a number. Where am I going wrong?

Regards.

DECLARE @SQL_STMT VARCHAR(MAX) = ''

SELECT @SQL_STMT = @SQL_STMT
    + '''ALTER TABLE '
    + TABLE_NAME
    + ' ALTER COLUMN '
    + COLUMN_NAME
    + ' '
    + DATA_TYPE
    + '('' SELECT MAX(LEN('
    + COLUMN_NAME
    + ')) FROM '
    + TABLE_NAME
    + ''') ''
    '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'

PRINT(@SQL_STMT)

You want to alter column of varchar upto how much length ??? This is really unclear what exactly you wants to do ?? — Krishnraj Rana, Sep 26 '14 at 15:08
up to the largest length of a single field. For example select max(len(empname)) from emp — Danny Rancher, Sep 26 '14 at 15:10
Why in the world would you do this? This could cripple your applications that use this database. You can start getting truncation of data errors because the front end has validation. You aren't going to gain anything from this because you are only changing varchar columns. Not to mention that you can possibly get errors because you are using LEN which does not take into account trailing spaces. — Sean Lange, Sep 26 '14 at 15:15
You should use [DATALENGTH](http://msdn.microsoft.com/en-us/library/ms173486.aspx) over `LEN` in this case, because, as @SeanLange mentioned, `LEN` will exclude trailing blanks. — DrCopyPaste, Sep 26 '14 at 15:25

Ndech · Accepted Answer · 2014-09-26T15:44:52.567

1

EDIT My first version was not refined enough, here is a new version that I have tested :

DECLARE @SQL_STMT VARCHAR(MAX) = 'DECLARE @query nvarchar(max);'

SELECT @SQL_STMT = @SQL_STMT
    + 'SET @query =''ALTER TABLE '
    + TABLE_NAME
    + ' ALTER COLUMN '
    + COLUMN_NAME
    + ' '
    + DATA_TYPE
    + '('' +CAST((SELECT MAX(DATALENGTH('
    + COLUMN_NAME
    + ')) FROM '
    + TABLE_NAME
    + ') as nvarchar(max))+'') ''
    exec(@query);'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'nvarchar'

PRINT(@SQL_STMT)

edited Sep 26 '14 at 15:44

answered Sep 26 '14 at 15:18

Ndech

965
10
21

My first version did not worked (I did not have a sandbox database to try), but I have edited my answer with a functional solution. – Ndech Sep 26 '14 at 15:34
Cheers mate! I'll although ill use datalength instead as @DrCopyPaste said – Danny Rancher Sep 26 '14 at 15:42
It is indeed a good suggestion, I will edit my answer for completeness. – Ndech Sep 26 '14 at 15:44

score 0 · Answer 2 · answered Sep 26 '14 at 15:44

I'm not a friend of the idea, but this should do what you want:

DECLARE @SQL_STMT VARCHAR(MAX) = ''

SELECT 
    IDENTITY(int,1,1) as ID, -- for later update   
     'ALTER TABLE '
    + c.TABLE_NAME
    + ' ALTER COLUMN '
    + c.COLUMN_NAME
    + ' '
    + c.DATA_TYPE
    + '('  P1

    , '  MAX(LEN('
    + c.COLUMN_NAME
    + ')'
    +') FROM '
    + c.TABLE_NAME P2

    , ') ' P3
into #tmp    
FROM INFORMATION_SCHEMA.Tables t 
JOIN INFORMATION_SCHEMA.Columns c 
on    c.TABLE_CATALOG= t.TABLE_CATALOG 
  and c.TABLE_SCHEMA=t.TABLE_SCHEMA
  and c.TABLE_NAME=t.TABLE_NAME
where TABLE_TYPE='BASE TABLE' -- only Tables not views
and DATA_TYPE = 'varchar'

Select @SQL_STMT=''  -- collect ID + Max info e.g. SELECT 1,   MAX(LEN(FirstName)) FROM user_info 
Select @SQL_STMT=@SQL_STMT + 'SELECT '+ CAST(ID as varchar(10)) + ', '+ P2 +CHAR(13) 
from #tmp

Declare @a table (ID Integer,Size Integer) -- table for ID and Len
--print @SQL_STMT

insert into @a Exec (@SQL_STMT)  -- fill table by executing block

                     -- define a minimum size if 0  
Update #tmp set P2 = Case When Size<1 then 1 else Size end  -- update p2
From @a a where a.ID = #tmp.ID

Select @SQL_STMT=''
Select @SQL_STMT=@SQL_STMT + P1 + P2 + P3 +CHAR(13) 
from #Tmp

print @SQL_STMT

Exec(@SQL_STMT)


Drop table #tmp

score -1 · Answer 3 · answered Sep 26 '14 at 15:22

You can't use a SELECT statement or an integer variable as the size of the column in an ALTER TABLE statement. As a general rule, you can't mix DDL (data description language, e.g., CREATE, ALTER, DROP) and DML (data manipulation language, e.g., SELECT, INSERT, UPDATE) in the same statement.

What this means is that you'll have to use dynamic SQL just to accomplish the task. You won't be able to use dynamic SQL to build static SQL that you can save and reuse. You'll need to retrieve the maximum length of the field and save it to a variable, and then construct the ALTER statement, and finally EXEC () the ALTER statement. The easiest way to do that is probably with a cursor through INFORMATION_SCHEMA.COLUMNS, but you can use doubly dynamic SQL, too. That is, dynamic SQL that itself generates dynamic SQL. That's much harder to debug, however.

Beyond that, I question the validity of what you're trying to do. Simply put, you should never need to use this code. The database schema should be relatively fixed because altering it has significant impacts on performance. By changing the size of a column, you're telling the database engine that it needs to change the way it stores the data on the disk physically, and that's not something you should be doing lightly or regularly. You'll be much further along just setting the value large enough to accommodate user needs. Modern RDBMSs are much better about not storing data inefficiently, but altering column sizes often is still poor practice.

Dynamic T-SQL - Alter column definition to max length of field

3 Answers3