使用 CASE 语句检查表中是否存在列 - SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16687579/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use CASE statement to check if column exists in table - SQL Server
提问by WheretheresaWill
I'm using a SQL serverstatement embedded in some other C# code; and simply want to check if a column exists in my table.
我正在使用嵌入在其他一些 C# 代码中的SQL 服务器语句；并且只想检查我的表中是否存在一列。
If the column (ModifiedByUSerhere) does exist then I want to return a 1or a true; if it doesn't then I want to return a 0or a false(or something similar that can be interpreted in C#).
如果列（ModifiedByUSer此处）确实存在，那么我想返回1或true；如果不是，那么我想返回一个0或一个false（或类似的东西，可以在 C# 中解释）。
I've got as far as using a CASE statement like the following:
我已经使用了如下的 CASE 语句：
SELECT cast(case WHEN EXISTS (select ModifiedByUser from Tags)
THEN 0
ELSE 1
END as bit)
But if the ModifiedByUser doesn't exist then I'm getting an invalid column name, instead of the return value.
但是如果 ModifiedByUser 不存在，那么我得到的是invalid column name, 而不是返回值。
I've also considered:
我也考虑过：
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Tags' AND COLUMN_NAME = 'ModifiedByUser')
BEGIN // Do something here to return a value
END
But don't know how to conditionally return a value/bool/bit based on the result. Any help much appreciated!
但是不知道如何根据结果有条件地返回一个值/布尔值/位。非常感谢任何帮助！
回答by WheretheresaWill
Final answer was a combination of two of the above (I've upvoted both to show my appreciation!):
最终答案是以上两个的组合（我对这两个都投了赞成票以表示感谢！）：
select case
when exists (
SELECT 1
FROM Sys.columns c
WHERE c.[object_id] = OBJECT_ID('dbo.Tags')
AND c.name = 'ModifiedByUserId'
)
then 1
else 0
end
回答by GSerg
select case
when exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Tags' AND COLUMN_NAME = 'ModifiedByUser')
then 0
else 1
end
回答by Mayank Awasthi
SELECT *
FROM ...
WHERE EXISTS(SELECT 1
FROM sys.columns c
WHERE c.[object_id] = OBJECT_ID('dbo.Tags')
AND c.name = 'ModifiedByUser'
)
回答by Devart
Try this one -
试试这个——
SELECT *
FROM ...
WHERE EXISTS(SELECT 1
FROM sys.columns c
WHERE c.[object_id] = OBJECT_ID('dbo.Tags')
AND c.name = 'ModifiedByUser'
)
回答by pratik garg
You can check in the system 'table column mapping' table
您可以在系统'表列映射'表中检查
SELECT count(*)
FROM Sys.Columns c
JOIN Sys.Tables t ON c.Object_Id = t.Object_Id
WHERE upper(t.Name) = 'TAGS'
AND upper(c.NAME) = 'MODIFIEDBYUSER'
