Monday, 27 August 2007

Checking object existence in SQL 2000 and SQL 2005

I have SQL 2005 on my machine but sometimes I am connecting to SQL 2000 database. If you generate drop object script from SQL 2005 Server Management Studio, it cannot be applied to the SQL 2000 database because they do not have the same system tables and views. For example dropping a stored procedure:

SQL 2005 syntax:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProc_Name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[StoredProc_Name]


However SQL 2000 database does not have sys.objects table. In SQL 2000 syntax:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StoredProc_Name]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[StoredProc_Name]


This will work in SQL 2005 database as well because SQL 2005 database has a view named sysobjects (for backward compatibility?!).

To check function existence in SQL 2000 syntax:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Function_Name]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[Function_Name]


To check table existence in SQL 2000 syntax:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Table_Name]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TableName]

No comments:

Post a Comment