Case statement to check if column exist in table

Question

Hi.
Is there an option to check , if a particular column name exist in a table using a query ? If exists, then execute a sql statement else execute another sql statement ?

How to achieve this ?
Thanks

Answer 1

Check if below query works

if exists(
select 1 from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'DimEmployee' and COLUMN_NAME = 'FirstName'
)
begin
select 'exists'
end

else

begin
select 'not exists'
end

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

I am getting error as - invalid column name

The query get's compile in once and if you have a query part which access a non-exists column (as I guess by your post) then compilation fails.
A simple CASE condition won't work for you.
You can try dynamic SQL, but that's not a big fun.

Answer 3

Hi，@Padmanabhan, Venkatesh
Welcome to the microsoft tsql Q&A forum!
Please check this:

DECLARE @column_name TABLE (Name nvarchar(50))  
INSERT INTO @column_name　　　　  
SELECT column_name FROM information_schema.columns   
WHERE table_name = 'TABLE_NAME To Search'   
  
IF EXISTS(select * from @column_name  
          where Name ='particular column name')  
SELECT 'a sql statement'  
ELSE   
SELECT 'another sql statement'   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

To avoid the compiler error, you must use "dynamic SQL'

DECLARE @sqlcmd VARCHAR(MAX);  
  
if exists(  
 select * from INFORMATION_SCHEMA.COLUMNS  
 where TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Customer' and COLUMN_NAME = 'Zip'  
 )  
 begin  
 SET @sqlcmd = '  
 SELECT CustomerId, CustomerName, Addr1, City, State, Zip  
 FROM dbo.Customer';  
 end  
      
 else  
      
 begin  
 SET @sqlcmd = '  
 SELECT CustomerId, CustomerName, Addr1, City, State, ZipCode  
 FROM dbo.Customer';  
 end  
 EXEC(@sqlcmd);  

However, you would be better off creating a VIEW which renames the column correctly.