This week I am working on extending an application that takes advantage of the MSSQL servers that proliferate my company. I am writing a number of changes, altering, creating and dropping tables, and unfortunately – the scripts have to be executed a number of times. This poses a simple challenge. I don’t want to be executing SQL that has already been executed on a previous run, so I have had to get creative in my queries in order to avoid this overlap. Below are a few of the tests that I am using.
Does a table exist?
IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Table]') AND type in (N'U') ) GO
Does a table not exist?
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Table]') AND type in (N'U') ) GO
Does a stored procedure exist?
IF EXISTS ( SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[usp_ProcedureName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) GO
Does a table have a field already?
IF EXISTS (SELECT Coalesce(Col_length('dbo.T_Table', 'FieldName'), 0)) GO
You can, quite obviously, append NOT to EXISTS in any of the context above for the alternate definition.
I would be posting more often, but I have finals around the corner and an imminent release of this application I have to complete by the end of the month as well. I am also back modding UT3, Expect videos soon