check if several fields are created in the database

Question

It was added in a table plus 7 columns, only before you need to check if this column is in the database otherwise the column creation script should not be executed.

Is there a way in a single condition to check if the columns have already been created in the bank? Follows 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 column creation script 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 1

you can use the example below, it checks if the columns exist in the in() if it does not exist it rotates the interval between the BEGIN and the END..

To identify the table columns I used the sys.Columns, when using the sys.Columns need to call the method Object_id() setting the table(schema) so he knows where to look. Remembering that Obeject_id() , returns the database identification number of the scheme 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 first-line.

Answer 2

I understood that you need to check if the seven fields exist, if that’s it, something very simple would be the following:

DECLARE @QTE_CAMPOS INT

SET @QTE_CAMPOS =
(SELECT COUNT(*)
FROM (
    SELECT 
        C.NAME   
    FROM 
        SYSCOLUMNS C 
        INNER JOIN SYSOBJECTS T 
        ON C.id = T.id
    WHERE
       T.xtype = 'U'                                                                                         
       AND T.NAME = 'NOME_TABELA'
       AND C.NAME IN ('CAMPO1','CAMPO2','CAMPO3','CAMPO4','CAMPO5','CAMPO6','CAMPO7')
) A
HAVING COUNT(*) = 7)

IF @QTE_CAMPOS = 7 
BEGIN
    Print 'NAO CRIA CAMPOS'
END
ELSE
BEGIN
    Print 'CRIA CAMPOS'
END

That is, the above SQL returns 7 if the 7 fields exist and nothing if there is a different amount.

Note, however, that the 7 fields cannot exist. Otherwise, you will have problems creating.

Although I have given this answer, I believe that the most defensive is to make an IF for each field checking if it exists, if it does not exist, performs the creation. There are only 7 fields. A check for each won’t cost many lines of code.