How to check if a table exists in SQL Server 2005 database and if it does not exist create it and the columns

1

How to check if a table exists in the SQL Server 2005 database, and if it does not exist, create it and the columns. The purpose of this process and create this routine for when I need to create a new field in my application, I put it inside this process, so as not to run the risk of at any time generating an error by missing a table or field. >     

asked by anonymous 05.05.2016 / 23:44

2 answers

9

There is the INFORMATION_SCHEMA view that allows you to do the verification, and one of the advantages of it is that it is defined by default in different databases and versions of the DBMS.

See how to implement:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Faça algo aqui...
END

Source: Check if table exists in SQL Server .

    
06.05.2016 / 00:00
3

Another interesting way is to check for the Object_Id function in Sql Server 2008 I'm sure it works, in 2005 you'll have to do the test.

if object_id('TABELA') is null
begin
     create Tabela (
            campo tipo,
            campo2 tipo
     )
end 

To check if the column exists, it will list all the columns of the desired table, only implement the logic to see if the field you want to create no longer exists in it

select b.name as 'Coluna'
  from sys.tables a
  join sys.columns b on a.object_id = b.object_id
 where a.name = 'tabela'
    
09.05.2016 / 23:22