这是代码:
IF EXISTS(SELECT * FROM sys.columns
WHERE Name = 'columnName'
AND OBJECT_ID = OBJECT_ID('tableName') )
BEGIN
PRINT 'Your Column Exists'
END
我想知道如何将这段代码转换为可以检查多个列名存在性的代码。
这是代码:
IF EXISTS(SELECT * FROM sys.columns
WHERE Name = 'columnName'
AND OBJECT_ID = OBJECT_ID('tableName') )
BEGIN
PRINT 'Your Column Exists'
END
我想知道如何将这段代码转换为可以检查多个列名存在性的代码。
如果您正在测试是否所有列都在那里,可以使用:
IF 3 = (select count(*) Names
from sys.columns
where OBJECT_ID = OBJECT_ID('tableName')
and Name in ('columnName1', 'columnName2', 'columnName3')
)
BEGIN
PRINT 'Your Columns Exist'
END
类似但更易理解的内容可能是:
IF EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table Name'
AND(COLUMN_NAME = 'column 1'
or COLUMN_NAME = 'column 2'
or COLUMN_NAME = 'column 3'
or COLUMN_NAME = 'column 4')
)
SELECT 'Column exists in table' AS[Status];
ELSE
SELECT 'Column does not exist in table' AS[Status];
这个怎么样?
IF EXISTS (SELECT * FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID('tableName')
AND( Name = 'columnName1' OR Name = 'columnName2' OR Name = 'columnName3')
)
BEGIN
PRINT 'Your Columns Exist'
END