如果不存在,我需要添加一个特定的列。我有类似以下的内容,但它总是返回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数据库的表中是否存在列?
当前回答
IF NOT EXISTS(SELECT NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TableName'
AND table_schema = 'SchemaName'
AND column_name = 'ColumnName') BEGIN
ALTER TABLE [SchemaName].[TableName] ADD [ColumnName] int(1) NOT NULL default '0';
END;
其他回答
IF NOT EXISTS(SELECT NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TableName'
AND table_schema = 'SchemaName'
AND column_name = 'ColumnName') BEGIN
ALTER TABLE [SchemaName].[TableName] ADD [ColumnName] int(1) NOT NULL default '0';
END;
declare @myColumn as nvarchar(128)
set @myColumn = 'myColumn'
if not exists (
select 1
from information_schema.columns columns
where columns.table_catalog = 'myDatabase'
and columns.table_schema = 'mySchema'
and columns.table_name = 'myTable'
and columns.column_name = @myColumn
)
begin
exec('alter table myDatabase.mySchema.myTable add'
+' ['+@myColumn+'] bigint null')
end
有几种方法可以检查列的存在。我强烈建议使用INFORMATION_SCHEMA.COLUMNS,因为它是为了与用户通信而创建的。考虑下表:
sys.objects
sys.columns
甚至可以使用一些其他访问方法来检查系统目录。
此外,无需使用SELECT*,只需通过NULL值测试即可
IF EXISTS(
SELECT NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName'
)
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
接受答案的临时表格版本:
if (exists(select 1
from tempdb.sys.columns
where Name = 'columnName'
and Object_ID = object_id('tempdb..#tableName')))
begin
...
end