verify that multiple fields are created in the database

Question

verify that multiple fields are created in the database

Navigation

#1 by (2 votes)
#2 by (1 votes)

4

A further 7 columns have been added to a table, but you must first check if this column is in the database, otherwise you should not run the column creation script.

Is it possible, in a single condition, to check whether the columns have already been created in the database? Here is 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

sql sql-server

asked by anonymous 25.08.2015 / 15:57

2 answers

2

You can use the example below, it checks whether the columns exist in in () if they do not exist, it rotates the interval between BEGIN and END ..

To identify the columns of the table I used sys.columns , using sys.columns I need to call the Object_ID () setting the table (schema) so he knows where to look. Remember that Obeject_ID () , returns the bank ID 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.

25.08.2015 / 16:11

How to Distribute RIA with JNLP and Java 8 How to call procedure using Asp.net MVC with Entity Framework

score 1 · Accepted Answer

I understood that you need to check if the seven fields exist, if it is, 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 all 7 fields exist and nothing if there is a different amount.

Note, however, that the 7 fields can not exist. Otherwise, you'll have trouble creating it.

Although I have given this answer, I think the most defensive is to make an IF for each field checking if it exists, if it does not exist, it does the creation. There are only 7 courses. A check for each one will not cost many lines of code.