ALTER COLUMN IF EXISTS

Question

I would like to alter the table if the table has the column with same data type and number exists

Original tTable structure is

TableName

ColumnName NVARCHAR(100)

Code for altering column if ColumnName with NVARCHAR and length 100 exists

IF EXISTS(...)
BEGIN
    ALTER TABLE [dbo].[TableName]
    ALTER COLUMN [ColumnName] NVARCHAR(200) [NULL|NOT NULL]
END

What find query I need to insert at IF EXISTS(...)?

Rashida

Why you tag 3 sql-server versions??? just put the version you are working on !!! – Intrigante

Answer 1

I personally always opt for the SQL Server system views rather than the INFORMATION_SCHEMA for reasons detailed by Aaron Bertrand. The added advantage is that in this situation you can exclude computed columns, which just appear as normal columns in the table INFORMATION_SCHEMA.COLUMNS.

IF EXISTS
    (   SELECT  1
        FROM    sys.columns c
                INNER JOIN sys.types t
                    ON t.system_type_id = c.system_type_id
                    AND t.user_type_id = c.user_type_id
        WHERE   c.name = 'ColumnName'
        AND     c.[object_id] = OBJECT_ID(N'dbo.TableName', 'U')
        AND     t.name = 'nvarchar'
        AND     c.max_length = 100
        AND     c.is_computed = 0
    )
    BEGIN
        ALTER TABLE [dbo].[TableName]
        ALTER COLUMN [ColumnName] NVARCHAR(200) [NULL|NOT NULL]
    END;

As shown in this SQL Fiddle when using the information schema method you may try and alter a computed column and get an error.

Bastard

Agree (+1) regarding the system views, though I prefer the metadata functions for things like this. – Tennyson

Answer 2

Me, I'm way too lazy to type in all those system table joins (let alone the INFORMATION_YADA schema), I just use the metadata functions:

IF columnproperty(object_id('dbo.TableName'), 'ColumnName', 'ColumnId') is not null
 and columnproperty(object_id('dbo.TableName'), 'ColumnName', 'Precision') = 200
    ALTER...

I believe this works for SQL 2005, but you'll need to check.

Tennyson

Forgot to mention, I prefer this form as it's a lot easier to figure out what the IF statement is doing... – Tennyson

Answer 3

IF EXISTS(SELECT 1 
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo'
          AND COLUMN_NAME = 'ColumnName' AND DATA_TYPE = 'nvarchar'
          AND CHARACTER_MAXIMUM_LENGTH = 100)
BEGIN
ALTER TABLE [dbo].[TableName]
ALTER COLUMN [ColumnName] NVARCHAR(200) [NULL|NOT NULL]
END

Amyloid

Where you checking length 100 exists?? – Intrigante

well spotted @huMptyduMpty fixed it now :) – Amyloid

Answer 4

SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'TableName'

from this u get column name and type in form of cursor, consider each row and process rest

Colet

Answer 5

Try this

IF EXISTS (SELECT data_type FROM Information_Schema.Columns WHERE Table_Name = 'MyTable'
      AND Column_Name = 'MyColumn' AND data_type = 'NVARCHAR ' AND character_maximum_length =100)
BEGIN
ALTER TABLE [dbo].[TableName]
ALTER COLUMN [ColumnName] NVARCHAR(200) [NULL|NOT NULL]
END

Lavonlavona

Where you checking length 100 exists?? – Intrigante

Answer 6

For some reason, if you try to rename a column that does not exist, PostgreSQL will issue an error. Unfortunately that PostgreSQL does not provide the IF EXISTS option for the RENAME clause.

Menashem