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

6 Answers

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.

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 

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.

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

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 

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.