CODEWITHSUNDEEP
sql-serverdynamic-sqlalter-column
Danny Rancher
Danny Rancher

Reputation: 2005

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

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)

Upvotes: 1

Views: 2245

Answers (4)

Trung Kim
Trung Kim

Reputation: 1

SET @current_date = CURDATE(); SET @end_date = DATE_ADD(@current_date, INTERVAL 2 YEAR);

SET @sql = '';

-- Loop through all months from the current date to two years later WHILE @current_date <= @end_date DO -- Construct the table name based on current year and month SET @current_year = YEAR(@current_date); SET @current_month = MONTH(@current_date); SET @table_name = CONCAT('pc_outlook_log', LPAD(YEAR(@current_date) % 100, 2, '0'), LPAD(@current_month, 2, '0'));

SET @check_table = 
    CONCAT('SELECT COUNT(*) INTO @table_exists FROM information_schema.tables ',
           'WHERE table_schema = ''your_database'' ', -- Replace with your database name
           'AND table_name = ''', @table_name, ''';');

PREPARE stmt FROM @check_table;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

IF @table_exists > 0 THEN
    SET @sql = CONCAT(@sql, 
        'ALTER TABLE ', @table_name, 
        ' MODIFY COLUMN message VARCHAR(512), ', 
        ' MODIFY COLUMN status VARCHAR(50); ', 
        CHAR(10));
END IF;

SET @current_date = LAST_DAY(@current_date) + INTERVAL 1 DAY;

END WHILE;

SELECT @sql AS dynamic_sql;

Upvotes: -1

Ndech
Ndech

Reputation: 965

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)

Upvotes: 1

bummi
bummi

Reputation: 27385

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

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32220

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.

Upvotes: -1

Related Questions