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

Asked

Viewed 19,181 times

2

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 is to create this routine for when I need to create a new field in my application, I put it within this process, not to run the risk of at some point generating an error by being missing a table or field.

sql sql-server delphi-7

2 answers

12

There is the view INFORMATION_SCHEMA which makes it possible for you to check, and one of its advantages is that it is set by default in different databases and versions of 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.

3

Another interesting way is to check by the Object_id function in Sql Server 2008 I’m sure it works, in 2005 will 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 columns of the desired table, just 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'

Browser other questions tagged sql sql-server delphi-7

You are not signed in. Login or sign up in order to post.