CODEWITHSUNDEEP
sqlsql-serveralter-tablealter
Edito
Edito

Reputation: 3747

How to change CHARACTER_MAX_LENGTH of specific tables

I have multiple tables in which I have columns that need to get linked through foreign keys, problem is that some of those columns have different character_max_length how do I update those specific tables?

An example select statement for a column:

SELECT column_name, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE UPPER(column_name) LIKE '%MUNT%'

which gives me this:

enter image description here

now lets say I have to change all of them to 5

I've used this code before to change the datatypes, maybe this can help a bit?

Thank you for considering my question

Upvotes: 0

Views: 657

Answers (2)

Jens
Jens

Reputation: 69470

Have you tried:

Alter tabel tableName alter column columnName NVARCHAR(5)

Or try this more generic version:

use [YourDatabaseName]

declare @tn nvarchar(128)
declare @cn nvarchar(128)
declare @sql as nvarchar(1000)

declare c cursor for 
    select table_name,column_name
    from information_schema.columns 
    where TABLE_NAME not in (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS)
    AND UPPER(column_name) LIKE '%MUNT%'
open c
fetch next from c into @tn, @cn

while @@FETCH_STATUS = 0
begin
    set @sql = 'alter table ' + @tn + ' alter column ' 
        + @cn + ' nvarchar(5)'
    exec sp_executesql @sql
    fetch next from c into @tn, @cn
end
close c
deallocate c

Upvotes: 1

podiluska
podiluska

Reputation: 51504

You change the table definition, either through the user interface, or using ALTER TABLE

Upvotes: 0

Related Questions