Code Example #4 – MSSQL Tests

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?
Does a table not exist?
Does a stored procedure exist?
Does a table have a field already?

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

Leave a Reply