Check if Column exists before creating it on SQL SERVER

If you need script automation, checking if a table exists is fairly easy.

IF Object_id('[mytables].[DEPOSITO]') IS NULL 
  CREATE TABLE [mytables].[deposito] 
    ( 
       codigo INT 
    ) 

But what if you need to check if a column exists? You can try different aproaches to it, like:

IF NOT EXISTS(SELECT * 
              FROM   sys.columns 
              WHERE  NAME = N'YourColumn' 
                     AND object_id = Object_id(N'YourTable')) 
  BEGIN 
      ALTER TABLE yourtable 
        ADD yourcolumn INT NULL; 
  END 

Thats not so bad.... but looks like a lot of code to type... =P. How about using col_length to the task? It returns the length of the column, and if the column does not exists, it returns null;

  SELECT Col_length('YourTable', 'YourColumn') 

And here is full length example on how to use COL_LENGTH to check if a column exists...


CREATE SCHEMA mytables; 
go 

IF Object_id('[mytables].[DEPOSITO]') IS NOT NULL 
  DROP TABLE [mytables].[deposito]; 

CREATE TABLE [mytables].[deposito] 
  ( 
     [depositoid]    [BIGINT] IDENTITY(1, 1) NOT NULL, 
     [nome_deposito] [VARCHAR](50) NOT NULL, 
     [ativo]         [BIT] NULL 
  ); 

go 

IF Col_length('mytables.DEPOSITO', 'POSICAO') IS NULL 
  BEGIN 
      PRINT 'Column doesnt exist!' 

      ALTER TABLE [mytables].deposito 
        ADD posicao BIT NULL; 
  END; 

go 

Sp_help '[mytables].[DEPOSITO]'; 


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06