CODEWITHSUNDEEP
sql-server
Matt Balent
Matt Balent

Reputation: 2397

SQL Server : ALTER COLUMN only if existing column has a smaller length

I am trying to create a script which will alter a column only if it is not of the correct size. Something like this.

IF NOT EXISTS (SELECT COL_LENGTH('dbo.TSC701_OCT_CONTEXT', 'sql_stmt') = 1000 )
BEGIN
    ALTER TABLE dbo.TSC701_OCT_CONTEXT 
        ALTER COLUMN sql_stmt VARCHAR(1000)
END

Sorry for the screwed up syntax

Upvotes: 2

Views: 3129

Answers (4)

Ben Hoffman
Ben Hoffman

Reputation: 8259

You actually do not need the SELECT statement in the IF clause.

IF (COL_LENGTH('dbo.TSC701_OCT_CONTEXT', 'sql_stmt') < 1000)
BEGIN
    ALTER TABLE [Table Name]
    ALTER COLUMN [Column Name] varchar(1000) null
END

Also, if your column needs to be NOT NULL or is already set to NOT NULL, you will want to switch the null from above out for NOT NULL.

If your goal is to check every column in a database, you can use the below code. Just be aware this could be very slow and could cause unintended errors and issues. I would backup whatever database you plan to use this on immediately before running it and I would also run it on a small test database or one table to see if it does what you want without taking too long.

DECLARE @TABLE_CATALOG NVARCHAR(128), @TABLE_SCHEMA NVARCHAR(128), @TABLE_NAME NVARCHAR(128), @COLUMN_NAME NVARCHAR(128), @DATA_TYPE NVARCHAR(128)
DECLARE @IS_NULLABLE NVARCHAR(3)
DECLARE @sql NVARCHAR(1000)

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='teoe' AND CHARACTER_MAXIMUM_LENGTH < 1000)
BEGIN
    SELECT TOP 1 
        @TABLE_CATALOG = TABLE_CATALOG, 
        @TABLE_SCHEMA = TABLE_SCHEMA, 
        @TABLE_NAME = TABLE_NAME, 
        @COLUMN_NAME = COLUMN_NAME, 
        @IS_NULLABLE = IS_NULLABLE, 
        @DATA_TYPE = DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='teoe'
    AND CHARACTER_MAXIMUM_LENGTH < 1000

    SET @sql = 'ALTER TABLE [' + @TABLE_CATALOG + '].' + @TABLE_SCHEMA + '.[' + @TABLE_NAME + '] ALTER COLUMN [' + @COLUMN_NAME + '] '+ @DATA_TYPE + '(1000) ' +
        CASE WHEN @IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END
    EXECUTE @sql
    BREAK

END

Upvotes: 1

Matt Balent
Matt Balent

Reputation: 2397

Here is what I ended up with:

IF NOT EXISTS (SELECT 1 FROM sys.columns 
            WHERE name = 'sql_stmt' AND object_id = object_id('CONTEXT') and max_length = 1000)
BEGIN
    ALTER TABLE dbo.CONTEXT ALTER COLUMN [sql_stmt] varchar (1000) NULL
END

GO

Upvotes: 0

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

IF 1000 = (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='Database Name' AND TABLE_NAME='Table Name' AND COLUMN_NAME='Column Name')
BEGIN
    ALTER TABLE [Table Name]
    ALTER COLUMN [Column Name] varchar(1000) null
END

Upvotes: 0

mxix
mxix

Reputation: 3659

You can try it like this.

IF (SELECT CASE WHEN COL_LENGTH('dbo.TSC701_OCT_CONTEXT', 'sql_stmt') <> 1000 THEN 1 END) = 1
BEGIN ALTER TABLE dbo.TSC701_OCT_CONTEXT alter column sql_stmt varchar(1000) END

Upvotes: 0

Related Questions