--> (Word) | --> (PDF) | --> (Epub) | --> (Text) |
--> (XML) | --> (OpenOffice) | --> (XPS) | |
SCENARIO
I need to add a specific column if it does not exist. I have something like this, but it always returns false:
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName')
How can I check if a column exists on a table in SQL Server?
SOLUTION
SQL Server 2005 onwards:
if Exists(select * from sys.columns where Name = N'columnName' and Object_ID = Object_ID(N'tableName'))
begin
-- Column Exists
end
Or, as an alternative, you could use a more concise version, using COL_LENGTH:
IF COL_LENGTH('table_name','column_name') IS NULL
BEGIN
/*Column does not exist or caller does not have permission to view the object*/
END
SOLUTION (Function Version)
Try something like:
CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100)) RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
SET @Result = 'T'
END
ELSE
BEGIN
SET @Result = 'F'
END
RETURN @Result;
END
GO
GRANT EXECUTE ON [ColumnExists] TO [whoever]
GO
Then use it like this:
IF ColumnExists('xxx', 'yyyy') = 'F'
BEGIN
ALTER TABLE xxx
ADD yyyyy varChar(10) NOT NULL
END
GO
Ity should work on both SQL Server 2000 & SQL Server 2005.
SOURCE | LINK (Stackoverflow.com) | LANGUAGE | ENGLISH |