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.
