sql – check if multiple fields are created in the database

Question:

More 7 columns were added to a table, but first it is necessary to check if this column is in the database otherwise the column creation script must not be executed.

How in a single condition to check if the columns have already been created in the database? Here's the code:

IF NOT EXISTS (SELECT * 
               FROM SYSCOLUMNS C 
               INNER JOIN SYSOBJECTS T ON C.id = T.id 
               WHERE C.name = ('IdUserPreparo') 
               AND T.name = 'ComandaItem' 
               AND ('dtSolicitacao') 
               AND T.name = 'ComandaItem' 
               AND ('dtPreparo') 
               AND T.name = 'ComandaItem'
               AND ('idUserCancel') 
               AND T.name = 'ComandaItem'
               AND ('dtCancel') 
               AND T.name = 'ComandaItem'
               AND ('IsCancelado') 
               AND T.name = 'ComandaItem'
               AND ('obsCancel') 
               AND T.name = 'ComandaItem')
BEGIN

This is the script for creating the columns in the database:

ALTER TABLE dbo.ComandaItem ADD
    IdUserPreparo int NULL,
    dtSolicitacao datetime NULL,
    dtPreparo datetime NULL,
    idUserCancel int NULL,
    dtCancel datetime NULL,
    IsCancelado bit NULL,
    obsCancel varbinary(5000) NULL

GO
ALTER TABLE dbo.ComandaItem ADD CONSTRAINT
    FK_ComandaItem_PessoaPreparo FOREIGN KEY
    (
    IdUserPreparo
    ) REFERENCES dbo.Pessoa
    (
    IDCadastro
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.ComandaItem ADD CONSTRAINT
    FK_ComandaItem_PessoaCancel FOREIGN KEY
    (
    idUserCancel
    ) REFERENCES dbo.Pessoa
    (
    IDCadastro
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.ComandaItem SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Answer:

you can use the example below, it checks if the columns exist in in() if they don't exist it runs the interval between the BEGIN and the END ..

To identify the table columns I used the sys.columns , when using the sys.columns I need to call the Object_ID() method setting the table (schema) so it knows where to look for. Remembering that Obeject_ID() , returns the database identification number of the schema scope object.

IF NOT EXISTS(SELECT * FROM sys.columns 
            WHERE Name in ('coluna1', 'coluna2') AND Object_ID = Object_ID(N'suaTabela'))
BEGIN

    -- se as colunas que se encontram no in() não existirem ele irá executar o bloco que você colocar entre o BEGIN e o END

END

if you need to run if the columns exist just remove the NOT from the first line.