DECLARE @Name VARCHAR(50)
DECLARE @Nullable BIT

select @Name = name, @Nullable=isnullable from syscolumns (NOLOCK)
			  where name = 'TotalChange'

SET @Name = ISNULL(@Name,'')
SET @Nullable = ISNull(@Nullable, 1)

IF @Name <> '' AND @Nullable =1
BEGIN
	DECLARE @ConstraintName varchar(200)
	
	EXEC('Update AllotmentLines
	SET TotalChange = 0
	WHERE TotalChange IS NULL')

	DECLARE #cur CURSOR FOR 
		select s1.name from sysobjects s1
		inner join sysobjects s2 on s1.parent_obj = s2.id and s2.name = 'AllotMentLines' and s1.xtype = 'D'
	OPEN #cur
	FETCH NEXT FROM #cur INTO @ConstraintName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC('ALTER TABLE dbo.AllotmentLines DROP CONSTRAINT ' + @ConstraintName)
		FETCH NEXT FROM #cur INTO @ConstraintName
	END
	CLOSE #cur
	DEALLOCATE #cur

	DROP INDEX [IX_AllotMentLines] ON dbo.AllotmentLines

	ALTER TABLE dbo.AllotmentLines ALTER COLUMN TotalChange INT NOT NULL

	ALTER TABLE dbo.AllotmentLines
	  ADD CONSTRAINT DF_AllotMentLines_TotalChange
	  DEFAULT 0 FOR TotalChange

	ALTER TABLE [dbo].[AllotMentLines] 
		ADD  CONSTRAINT [DF_AllotMentLines_InitialCount]  
		DEFAULT (0) FOR [InitialCount]

	CREATE NONCLUSTERED INDEX [IX_AllotMentLines] ON [dbo].[AllotMentLines]
	(
		[AllotmentNumber] ASC,
		[DateStamp] ASC,
		[InitialCount] ASC,
		[TotalChange] ASC
	)


END