Use Case Statement to Check If Column Exists in Table - Sql Server
I'm Using a Sql Server Statement Embedded in Some Other C# Code; and Simply Want to Check If a Column Exists in My Table. If the Column (Modifiedbyuser Here)...
I'm using a SQL server statement embedded in some other C# code; and simply want to check if a column exists in my table.
If the column (ModifiedByUSer here) does exist then I want to return a 1 or a true; if it doesn't then I want to return a 0 or a false (or something similar that can be interpreted in C#).
I've got as far as using a CASE statement like the following:
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.
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!
5 Answers
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
select case
when exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Tags' AND COLUMN_NAME = 'ModifiedByUser')
then 0
else 1
end
SELECT *
FROM ...
WHERE EXISTS(SELECT 1
FROM sys.columns c
WHERE c.[object_id] = OBJECT_ID('dbo.Tags')
AND c.name = 'ModifiedByUser'
)
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'
)
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'
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
