I want to write a query for MS SQL Server that adds a column into a table. But I don't want any error display, when I run/execute the following query.
I am using this sort of query to add a table ...
IF EXISTS (
SELECT *
FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Person]')
AND TYPE IN (N'U')
)
But I don't know how to write this query for a column.
This question is related to
sql-server
add
not-exists
You can use a similar construct by using the sys.columns
table io sys.objects
.
IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'[dbo].[Person]')
AND name = 'ColumnName'
)
Here's another variation that worked for me.
IF NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE upper(TABLE_NAME) = 'TABLENAME'
AND upper(COLUMN_NAME) = 'COLUMNNAME')
BEGIN
ALTER TABLE [dbo].[Person] ADD Column
END
GO
EDIT: Note that
INFORMATION_SCHEMA
views may not always be updated, useSYS.COLUMNS
instead:
IF NOT EXISTS (SELECT 1
FROM SYS.COLUMNS....
When checking for a column in another database, you can simply include the database name:
IF NOT EXISTS (
SELECT *
FROM DatabaseName.sys.columns
WHERE object_id = OBJECT_ID(N'[DatabaseName].[dbo].[TableName]')
AND name = 'ColumnName'
)
IF NOT EXISTS (SELECT * FROM syscolumns
WHERE ID=OBJECT_ID('[db].[Employee]') AND NAME='EmpName')
ALTER TABLE [db].[Employee]
ADD [EmpName] VARCHAR(10)
GO
I Hope this would help. More info
Another alternative. I prefer this approach because it is less writing but the two accomplish the same thing.
IF COLUMNPROPERTY(OBJECT_ID('dbo.Person'), 'ColumnName', 'ColumnId') IS NULL
BEGIN
ALTER TABLE Person
ADD ColumnName VARCHAR(MAX) NOT NULL
END
I also noticed yours is looking for where table does exist that is obviously just this
if COLUMNPROPERTY( OBJECT_ID('dbo.Person'),'ColumnName','ColumnId') is not null
IF NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE
OBJECT_ID = OBJECT_ID(N'[dbo].[Person]') AND name = 'DateOfBirth')
BEGIN
ALTER TABLE [dbo].[Person] ADD DateOfBirth DATETIME
END
IF COL_LENGTH('table_name', 'column_name') IS NULL
BEGIN
ALTER TABLE table_name
ADD [column_name] INT
END
Source: Stackoverflow.com