如果不存在,我需要添加一个特定的列。我有类似以下的内容,但它总是返回false:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName')
如何检查SQL Server数据库的表中是否存在列?
如果不存在,我需要添加一个特定的列。我有类似以下的内容,但它总是返回false:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName')
如何检查SQL Server数据库的表中是否存在列?
当前回答
尝试以下操作:
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
然后这样使用:
IF ColumnExists('xxx', 'yyyy') = 'F'
BEGIN
ALTER TABLE xxx
ADD yyyyy varChar(10) NOT NULL
END
GO
它应该可以在SQL Server 2000和SQL Server 2005上运行。我不确定SQL Server 2008,但我不知道为什么不这样做。
其他回答
试试这个。。。
IF NOT EXISTS(
SELECT TOP 1 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
[TABLE_NAME] = 'Employees'
AND [COLUMN_NAME] = 'EmployeeID')
BEGIN
ALTER TABLE [Employees]
ADD [EmployeeID] INT NULL
END
还有一种变化。。。
SELECT
Count(*) AS existFlag
FROM
sys.columns
WHERE
[name] = N 'ColumnName'
AND [object_id] = OBJECT_ID(N 'TableName')
以下查询可用于检查表中是否存在搜索列。我们可以根据搜索结果做出决定,如下所示。
IF EXISTS (SELECT 'Y' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <YourTableName> AND COLUMN_NAME = <YourColumnName>)
BEGIN
SELECT 'Column Already Exists.'
END
ELSE
BEGIN
ALTER TABLE <YourTableName> ADD <YourColumnName> <DataType>[Size]
END
下面是我用来管理数据库中列添加的简单脚本:
IF NOT EXISTS (
SELECT *
FROM sys.Columns
WHERE Name = N'QbId'
AND Object_Id = Object_Id(N'Driver')
)
BEGIN
ALTER TABLE Driver ADD QbId NVARCHAR(20) NULL
END
ELSE
BEGIN
PRINT 'QbId is already added on Driver'
END
在此示例中,Name是要添加的ColumnName,Object_Id是TableName
SQL Server 2005及以后版本:
IF EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N'columnName'
AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
-- Column Exists
END
马丁·史密斯的版本更短:
IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
-- Column Exists
END