Microsoft SQLServer: Testing If Table/Column Exists

Sometimes I have existing customers who need their database upgrading to a newer version – new tables or columns need to be added. I always provide SQL scripts for this but sometimes I’m unsure whether the customer or their IT people have run the last script, or even the ones before that.

Often web pages fall over and never mention you have a certain table or column missing from the database and it can be a real problem to debug on-site with remote servers. So what I do is attempt to re-create the entire database by testing what already exists. If it doesn’t exist I create it, simple as that.

At the end of the day I at least know that the database schema is up-to-date.

Let’s say I need to have a table called ‘Settings’. The script below first tests if the table exists. If it does then I move on otherwise I run the normal CREATE TABLE.


PRINT 'CREATING TABLE Settings'
IF OBJECT_ID ('[Settings]','U') IS NOT NULL
PRINT '-- EXISTS: TABLE Settings'
ELSE
CREATE TABLE [Settings] (
 [ID] [int] NOT NULL DEFAULT ((0)),
 [AppName] [nvarchar] (50) NULL ,
 [AdminContact] [nvarchar] (50) NULL ,
 [AdminEmail] [nvarchar] (100) NULL ,
 CONSTRAINT [aaaaaSettings_PK] PRIMARY KEY NONCLUSTERED
 (
  [ID]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO


Even more common is adding a new column, or altering it’s properties. The example script below tests if a column called ‘TotalLogins’ exists in the ‘Settings’ table; if not it creates it.


if not exists (select * from syscolumns  where id=object_id('Settings') and name='TotalLogins')
 ALTER table Settings add TotalLogins [int] DEFAULT 0
GO


It can be worth while adding the conditional logic if the database is subject to periodic modifications.

The examples I’ve shown will only work for SQLServer, but I’m sure there will be equivalent functions in MySQL, Oracle and other database servers.

Les Kendall