If you, like me, have the misfortune to work with very creative software architects :D:D:D, you probably need to write Database Modification scripts on a daily base. It is a good practice be sure that a script executed two times in a row doesn’t throw exceptions, mainly if the script can be executed by your customers DBA (or the customers themselves). I write down here some of the most common tests, you can use when you have to Add tables, Drop Tables, Add Columns, Drop Columns or Foreign Keys.
IF NOT EXISTS (
SELECT 1 FROM sysobjects WHERE xtype='u'
AND name='MyTable')
BEGIN
CREATE TABLE Mytable …;
END
Without the NOT this can be used also to Drop Tables.
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID('[FK_MyForeignKeyConstraintName]')
AND parent_object_id = OBJECT_ID('[MyTable]'))
ALTER TABLE [MyTable] DROP CONSTRAINT [FK_MyForeignKeyConstraintName]
If you need to delete a table with Relations, it is better drop the Constraints before dropping the table. If you Add a Constraint you can set a NOT before Exists and avoid trying to create a constraint already present.
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('MyTable') AND name='MyColumnName')
ALTER TABLE MyTable ADD MyColumnName int NULL;
This one is useful when Adding, or without NOT, Dropping columns.