Use CASE statement to check if column exists in table - SQL Server

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT   FROM     WHERE EXISTS SELECT 1          FROM sys columns c         WHERE c  object id    OBJECT ID  dbo Tags               AND c name    ModifiedByUser

User · Answer

select case          when exists  SELECT 1 FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    Tags  AND COLUMN NAME    ModifiedByUser            then 0          else 1        end

[sql] Use CASE statement to check if column exists in table - SQL Server

Examples related to sql

Examples related to sql-server

Examples related to case