SQL Server How to select a column only if it exists in the table

Question

I would like to perform a SELECT where it selects a column value only if that column exists in the table, otherwise null is displayed.

This is what I am doing now:

SELECT TOP 10 CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName') THEN columnName ELSE NULL END AS columnName 

I also tried this:

 SELECT TOP 10 CASE WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName') >0 THEN columnName ELSE NULL END AS columnName 

Both of them work well if the column is present in the table. But when there is no column, it gives me an error:

Invalid column name 'columnName'

Answer 1

You can write as:

 SELECT CASE WHEN EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName' ) THEN ( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName' ) ELSE NULL END AS columnName 

Demo

Edit: If you want to select the top 10 values ​​from a table column, if this column exists, you need to write a dynamic query as:

 SELECT @columnVariable = CASE WHEN EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName' ) THEN ( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName' ) ELSE NULL END /* Build the SQL string one time.*/ SET @SQLString = N'SELECT TOP 10 ' + @columnVariable+ ' FROM test.tableName '; EXECUTE sp_executesql @SQLString 

Demo2

Answer 2

Try the following:

 SELECT TOP 1 CASE WHEN COLUMN_NAME='columnName' THEN COLUMN_NAME ELSE NULL END AS COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' ORDER BY COLUMN_NAME DESC 

Answer 3

 SELECT * FROM sys.columns WHERE [name] = N'columnName' AND [object_id] = OBJECT_ID(N'tableName') 

Add this to your case statement. Please note that this code will only work for a higher version of sqlserver. [Like sqlserver 2008]