Why does something like this not work?

ELSE Somecol
FROM Customers;

I am just checking if the column exists, however, SQL Server complains about Somecol not existing. Is there an alternative to this in a single statement?

  • 3
    Do you have an example for why you would want to do this? I can't understand why you'd want to write a query that attempts to select from a column that might not exist. Jun 6 '14 at 14:46
  • @MarkSinkinson Its not by choice, but a vendor, and cannot control it. Obviously prefer not to, but just the situation I am in. Jun 6 '14 at 15:52
  • @MarkSinkinson An example would be a scenario where the column might exist in the table or it might not. And you want to write the query once, embed it in the application, and have it handle a missing column correctly.
    – Ian Boyd
    Nov 18 '15 at 16:32
  • @MarkSinkinson - One example is here dba.stackexchange.com/a/151158/3690. Column filter_definition and is_incremental don't exist in all versions of sys.stats but using the trick in Andriy's answer I can write a script that works in all versions. Oct 1 '16 at 13:53

The following query uses the same idea as in this amazing answer by ypercube:

    SomeCol AS MyTest
  FROM dbo.Customers
) AS x;

It works like this:

  • if dbo.Customers has a column named SomeCol, then SomeCol in SomeCol AS MyTest will resolve as dbo.Customers.SomeCol;

  • if the table has no such column, the reference will still be valid, because now it will be resolved as dummy.SomeCol: dummy columns can be referenced in that context.

You can specify multiple "spare" columns that way. The trick is not to use the table alias for such columns (which is a frowned-upon practice in most situations, but in this case omitting the table alias helps you to resolve the issue).

If the table is used in a join and the other table has its own SomeCol, you will probably need to use the above query as a derived table before using it in the join in order to keep the trick working, something like this:

  SELECT x.*
  FROM (SELECT NULL AS SomeCol) AS dummy
      SomeCol AS MyTest
    FROM dbo.Customers
  ) AS x
) AS cust

One way to do this is to check for the columns existence, then build the Dynamic SQL based on whether that column exists or not.

Without Dynamic SQL, SQL Server will attempt to evaluate whether or not the column exists before it even executes the statment, resulting in an error.

It does, however, mean you will have 2 queries to write and potentially alter in future. But I don't believe you should really be targeting SELECT statements on columns that may not exist.

declare @SQL varchar(max)

If exists (select 1 from sys.columns where Name = N'NameOfColumn' and object_id=object_id(N'yourTableName'))
set @SQL = 'select ID, NameOfColumn from yourTableName'
Print 'Column does not exist'
  • Yes, makes sense, however, must be in a single statement. Ultimately, I am looking for probably some magic system function that does not exist. Jun 6 '14 at 15:53

You can make use of some XML to query columns that might be in the table.

Build an XML from all columns per row in a cross apply and extract the value using the values() function.

In this query ID is known so get it from the table directly. Col1 and Col2 might be there or not so get them using the XML.

select T.ID,
       TX.X.value('(Col1/text())[1]', 'int') as Col1,
       TX.X.value('(Col2/text())[1]', 'int') as Col2
from T
  cross apply (select T.* for xml path(''), type) as TX(X)

SQL Fiddle


My approach differs only slightly from the others. I prefer to use the system for this and simply get a count because you can assign the column count to a variable at the top of a query and then choose to proceed or not based on that. The downside to that is…if you have the same column name in multiple tables, you are uncertain that the column exists in the table you are wanting to query. However, the technique works on particular tables as well, since you are only looking to get a count.

The 'trouble' with asking for it specifically is -- the trouble you are experiencing. In general, if a NULL value causes you issues…find another way to verify existence. This is one way of doing that without risking upsetting the server.

SELECT COUNT(*) FROM sys.columns WHERE sys.columns.name = 'FarmID'
  • 1
    Why not use the sysobjects as well in your query to check whether the specific table has such a column? Jun 6 '14 at 17:31
  • Yes…I mentioned that could be done…you could even do the same on the particular table you are querying about…I just showed the general format for using COUNT because COUNT does not error when the COUNT is ZERO and…I suppose that I should mention that you can assign it to a variable, too. (e.g. SELECT COUNT(*) AS myVarName…)
    – jinzai
    Jun 6 '14 at 17:39
  • 1
    I can't see how this would be better than Mark's query. SELECT 1 ... does not error either. Jun 6 '14 at 17:42
  • I did not say it was better, but it is a far simpler way to achieve the same result. SELECT 1 may not error, but it is not the same thing as COUNT. SELECT returns SOMETHING…even if it is NULL. COUNT only has to return a single number. This way would be faster and I did mention that the count can be used later.
    – jinzai
    Jun 12 '14 at 19:32
  • If you need the count ok. But EXISTS (SELECT ...) is usually faster than (SELECT COUNT(*) ...), not the other way around. Jun 12 '14 at 19:39

If I understood it correctly...

You can use the query something like below and act accordingly based on the count... If the count is > 1 then it means you have the col in that table, and the count = 0 then you don't have that col in that table

SELECT count(*)
AND TABLE_SCHEMA='dbo' and TABLE_NAME ='UserBase' ;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.