CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2008-r2
Billa
Billa

Reputation: 5266

ALTER COLUMN IF EXISTS

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(...)?

Upvotes: 5

Views: 25818

Answers (6)

Makara Kann
Makara Kann

Reputation: 17

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.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40309

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.

Upvotes: 2

GarethD
GarethD

Reputation: 69759

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.

Upvotes: 4

M.Ali
M.Ali

Reputation: 69514

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

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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

Upvotes: 0

harshal
harshal

Reputation: 592

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

Upvotes: 0

Related Questions