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!

As you've discovered, it's better to add an answer below the question, to maintain the standard question-answer format. Thanks for adding it! — halfer
– halfer, Commented Jan 23, 2014 at 9:24

WheretheresaWill · Accepted Answer · 2013-07-01 08:29:38Z

55

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

answered Jul 1, 2013 at 8:29

WheretheresaWill

6,6088 gold badges34 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BVernon Over a year ago

Since the original post mentioned using something like C# to work with the result, I would suggest also casting the value to a bit in the sql. That way C# can actually work with the returned value as a bool.

GSerg · Accepted Answer · 2013-05-22 09:07:09Z

16

select case
         when exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Tags' AND COLUMN_NAME = 'ModifiedByUser')
         then 0
         else 1
       end

answered May 22, 2013 at 9:07

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

2 Comments

WheretheresaWill Over a year ago

Thanks for the answer, but it seems to be always returning 1, whether the column is present or not.

WheretheresaWill Over a year ago

I've combined your answer with the one below and it seems to work:

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

Radim Köhler · Accepted Answer · 2013-06-29 06:53:52Z

3

SELECT *
FROM ...
WHERE EXISTS(SELECT 1 
        FROM sys.columns c
        WHERE c.[object_id] = OBJECT_ID('dbo.Tags')
            AND c.name = 'ModifiedByUser'
    )

edited Jun 29, 2013 at 6:53

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

answered Jun 29, 2013 at 6:35

Mayank Awasthi

4912 silver badges9 bronze badges

Comments

Devart · Accepted Answer · 2013-05-22 09:28:50Z

2

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'
    )

edited May 22, 2013 at 9:28

answered May 22, 2013 at 9:09

Devart

120k23 gold badges172 silver badges189 bronze badges

3 Comments

Devart Over a year ago

@table_name like 'dbo.table1'

WheretheresaWill Over a year ago

How do I use this? Sorry not familiar with SQL and this is hard to get my head around.

WheretheresaWill Over a year ago

I've combined your answer with the one above and it seems to work:

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

pratik garg · Accepted Answer · 2013-05-22 09:35:36Z

1

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'

edited May 22, 2013 at 9:35

answered May 22, 2013 at 9:12

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

2 Comments

WheretheresaWill Over a year ago

Thanks but this is giving me an Invalid column name Table_Name for line 4. Any ideas why?

pratik garg Over a year ago

@WheretheresaWill .. My Bad.. I have change and corrected the query.. now check

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

2 Comments

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related