Good Practice - Schema Scripting

Dealing with multiple situtations involving lazy developers and offshored software houses means i am supplied a fair number of (often untested) scripts.

Here are some examples for those supplying schema modifications -

example 1 - When replacing an database object, check for its existance
and drop it first before attempting to create it.

IF EXISTS(select * from information_schema.tables
    where table_name ='FORECAST_BREAKDOWN')
BEGIN
 DROP TABLE [dbo].[FORECAST_BREAKDOWN]
END

 CREATE TABLE [dbo].[FORECAST_BREAKDOWN](
  [STACK_ID] [numeric](10, 0) NOT NULL,
  [PLOT_CODE] [varchar](20) NOT NULL,
  [FB_AMOUNT] [numeric](35, 10) NOT NULL CONSTRAINT [DF__FORECAST___FB_AM__54EB90A0]  DEFAULT ((0)),
  [DESCRIPTION] [varchar](255) NULL,
  [FB_DATE] [datetime] NULL)
go

example 2 - Check for the existence of a column before attempting to add it.

IF NOT EXISTS(select * from information_schema.columns
    where table_name ='forecast_breakdown'
    and column_name = 'srno')
BEGIN
 PRINT 'Column not present, creating...'
 ALTER TABLE [FORECAST_BREAKDOWN] ADD SRNO INT
END
ELSE
BEGIN
 PRINT 'Column already present'
END

These examples show how to script table and column changes. There are many more INFORMATION_SCHEMA views documented here > http://msdn.microsoft.com/en-us/library/ms186778.aspx