/*
	publ_QuoteItemsFinal

	Change the size of unnecessarily large columns
	Create search indexes
*/
--------------------------------------------------------------------------------------------------
IF EXISTS (
    SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID('dbo.publ_QuoteItemsFinal')
    AND name='idx_publ_QuoteItemsFinal_ParentSurfaceItemId')
BEGIN
	DROP INDEX [idx_publ_QuoteItemsFinal_ParentSurfaceItemId] ON [dbo].[publ_QuoteItemsFinal]
END
--------------------------------------------------------------------------------------------------
IF EXISTS (
    SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID('dbo.publ_QuoteItemsFinal')
    AND name='ix_publ_quoteItemsFinal_itemId')
BEGIN
	DROP INDEX [ix_publ_quoteItemsFinal_itemId] ON [dbo].[publ_QuoteItemsFinal]
END
--------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalPartNumber] [varchar](64) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalMasterPartNumber] [varchar](64) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalDescription] [varchar](512) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalBrand] [varchar](16) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalCondition] [varchar](16) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalLeadTime] [varchar](16) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalUniqueNumber] [varchar](64) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalCategory] [varchar](64) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalSubCategory] [varchar](64) NULL
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [QuoteItemsFinal_QuoteItemsFinal_QuoteItemsFinalMinorCode] [varchar](64) NULL
--------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------
IF COL_LENGTH('dbo.publ_QuoteItemsFinal', 'Supplier') IS NULL
BEGIN
    ALTER TABLE [dbo].[publ_QuoteItemsFinal]
		ADD [Supplier] [varchar](128) NULL
END
--------------------------------------------------------------------------------------------------
IF COL_LENGTH('dbo.publ_QuoteItemsFinal', 'Status') IS NULL
BEGIN
	ALTER TABLE [dbo].[publ_QuoteItemsFinal]
		ADD [Status] [bit] NULL
END
--------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------
UPDATE [publ_QuoteItemsFinal] SET [Status] = 0 WHERE [Status] IS NULL
--------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[publ_QuoteItemsFinal]
	ALTER COLUMN [Status] [bit] NOT NULL
--------------------------------------------------------------------------------------------------
IF OBJECT_ID('DBO.DF_publ_QuoteItemsFinal_Status', 'D') IS NULL
BEGIN
	ALTER TABLE [dbo].[publ_QuoteItemsFinal] 
		ADD CONSTRAINT [DF_publ_QuoteItemsFinal_Status]  DEFAULT ((0)) FOR [Status]
END
--------------------------------------------------------------------------------------------------
IF NOT EXISTS (
    SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID('dbo.publ_QuoteItemsFinal')
    AND name='ix_publ_quoteItemsFinal_itemId')
BEGIN
	CREATE NONCLUSTERED INDEX [ix_publ_quoteItemsFinal_itemId] ON [dbo].[publ_QuoteItemsFinal]
	(
		[itemID] ASC,
		[ParentSurfaceItemId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
--------------------------------------------------------------------------------------------------
IF NOT EXISTS (
    SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID('dbo.publ_QuoteItemsFinal')
    AND name='idx_publ_QuoteItemsFinal_ParentSurfaceItemId')
BEGIN
	CREATE NONCLUSTERED INDEX [idx_publ_QuoteItemsFinal_ParentSurfaceItemId] ON [dbo].[publ_QuoteItemsFinal]
	(
		[ParentSurfaceItemId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
--------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------