IF NOT EXISTS(SELECT *
                FROM sys.objects SO
               WHERE so.[name] = 'UDFCategory')
BEGIN

CREATE TABLE [dbo].[UDFCategory](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UDFCategory] [varchar](100) NULL,
 CONSTRAINT [PK_UDFCategory] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


END
GO

-- Add UDFCategory to the SystemUserDefinedFields Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'SystemUserDefinedFields'
   AND SC.[name] = 'UDFCategory'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].SystemUserDefinedFields'
      + char(13) + char(10) + '  ADD UDFCategory int NULL'

      EXEC (@command)

   END
GO


-- Add OrgClosedDate to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'OrgClosedDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD OrgClosedDate DateTime NULL'

      EXEC (@command)

   END
GO

-- Add ReOpenedDate to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'ReOpenedDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD ReOpenedDate DateTime NULL'

      EXEC (@command)

   END
GO


-- Add OrgClosedDate to the Closed Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Closed'
   AND SC.[name] = 'OrgClosedDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Closed'
      + char(13) + char(10) + '  ADD OrgClosedDate DateTime NULL'

      EXEC (@command)

      SET @command = 'Update Closed'
      + char(13) + char(10) + '  Set OrgClosedDate = DateClosed'

      EXEC (@command)

   END
GO


-- Add BillSent to the BillHeader Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'BillHeader'
   AND SC.[name] = 'BillSent'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].BillHeader'
      + char(13) + char(10) + '  ADD BillSent Char(1) NULL'

      EXEC (@command)

               SET @command = 'update [dbo].BillHeader'
      + char(13) + char(10) + '  Set BillSent = ''Y'' Where BillNo <> 0' -- set ALL bills to yes if they have an Invoice No..

      EXEC (@command)
               SET @command = 'update [dbo].BillHeader'
      + char(13) + char(10) + '  Set BillSent = ''N'' Where BillNo = 0' -- set ALL bills to No if they have no Invoice No, ie still in Draft..

      EXEC (@command)

   END
GO


IF NOT EXISTS(SELECT *
                FROM sys.objects SO
               WHERE so.[name] = 'CourtDateList')
BEGIN
CREATE TABLE [dbo].[CourtDateList](
	[Date] [datetime] NOT NULL,
	[WeekDay] [char](10) NULL,
	[Time] [float] NULL,
	[AmPM] [char](2) NULL,
	[CourtHour] [int] NULL,
	[CourtMinute] [int] NULL,
	[CourtAt] [datetime] NULL,
 CONSTRAINT [PK_CourtDateList] PRIMARY KEY CLUSTERED 
(
	[Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT *
                FROM sys.objects SO
               WHERE so.[name] = 'CourtDiary')
BEGIN
CREATE TABLE [dbo].[CourtDiary](
	[COURTDIARYRECORDNO] [int] IDENTITY(1,1) NOT NULL,
	[COURTDIARYAREACODE] [varchar](6) NULL,
	[COURTDIARYSEQ] [smallint] NULL,
	[COURTDIARYMONTHNO] [smallint] NULL,
	[COURTDIARYWEEKNO] [smallint] NULL,
	[COURTDIARYDAYNO] [smallint] NULL,
	[COURTDIARYTIME] [float] NULL,
	[COURTDIARYAMPM] [varchar](2) NULL,
 CONSTRAINT [PK_CourtDiary] PRIMARY KEY CLUSTERED 
(
	[COURTDIARYRECORDNO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

END
GO

-- Add Conv to the DebtAllocations Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'DebtAllocations'
   AND SC.[name] = 'Conv'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].DebtAllocations'
      + char(13) + char(10) + '  ADD Conv [Char] (1) NULL'

      EXEC (@command)

   END
GO

-- Add fees to the DiaryAttachments Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'DiaryAttachments'
   AND SC.[name] = 'Fees'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].DiaryAttachments'
      + char(13) + char(10) + '  ADD Fees int NULL'

      EXEC (@command)

               SET @command = 'update DiaryAttachments'
      + char(13) + char(10) + 'set Fees = 0'

      EXEC (@command)

   END
GO

-- Add outlays to the DiaryAttachments Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'DiaryAttachments'
   AND SC.[name] = 'Outlay'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].DiaryAttachments'
      + char(13) + char(10) + '  ADD Outlay int NULL'

      EXEC (@command)

               SET @command = 'Update DiaryAttachments'
      + char(13) + char(10) + '   Set Outlay = 0'

      EXEC (@command)

   END
GO

-- Add ArchPath to the DiaryAttachments Table  for the NEW closed matters 
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'DiaryAttachments'
   AND SC.[name] = 'ARCHPATH'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].DiaryAttachments'
      + char(13) + char(10) + '  ADD ARCHPATH Varchar(255) NULL'

      EXEC (@command)

   END
GO

-- Add OutlaySupplier to the DiaryAttachments Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'DiaryAttachments'
   AND SC.[name] = 'OutlaySupplier'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].DiaryAttachments'
      + char(13) + char(10) + '  ADD OutlaySupplier int NULL'

      EXEC (@command)

               SET @command = 'Update DiaryAttachments'
      + char(13) + char(10) + '   Set OutlaySupplier = 0'

      EXEC (@command)

   END
GO

-- Add EffectiveDate to the DebtCostingLedger Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'DebtCostingLedger'
   AND SC.[name] = 'EffectiveDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].DebtCostingLedger'
      + char(13) + char(10) + '  ADD EffectiveDate [DateTime] NULL'

      EXEC (@command)

   END
GO

-- Add CommercialOrNon to the Undertakings Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Undertakings'
   AND SC.[name] = 'CommercialOrNon'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Undertakings'
      + char(13) + char(10) + '  ADD CommercialOrNon varchar(1) NULL'

      EXEC (@command)

   END
GO

-- Add Notes to the Undertakings Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Undertakings'
   AND SC.[name] = 'Notes'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Undertakings'
      + char(13) + char(10) + '  ADD Notes varchar(3000) NULL'

      EXEC (@command)

   END
GO

-- Alter Description to 500 on the Undertakings Table
DECLARE @Size INT

SELECT @Size = SC.[length]
  FROM [sys].[objects] SO
       INNER JOIN [sys].[syscolumns] SC
               ON SC.[id] = SO.[object_id]
              AND SC.[name] = 'Description'
 WHERE SO.[name] = 'Undertakings'
   AND SO.[type] = 'U'

IF (@Size < 500)
  BEGIN
    ALTER TABLE [dbo].[Undertakings] ALTER COLUMN [Description] CHAR(500)
  END
GO

-- Add OutlayCode to the ChequeRequisitionHeader Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'ChequeRequisitionHeader'
   AND SC.[name] = 'OutlayCode'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].ChequeRequisitionHeader'
      + char(13) + char(10) + '  ADD OutlayCode char(4) NULL'

      EXEC (@command)

   END
GO

-- Alter Your Ref from 15 to 40 on the Matters Table
DECLARE @Size INT

SELECT @Size = SC.[length]
  FROM [sys].[objects] SO
       INNER JOIN [sys].[syscolumns] SC
               ON SC.[id] = SO.[object_id]
              AND SC.[name] = 'YourRef'
 WHERE SO.[name] = 'Matters'
   AND SO.[type] = 'U'

IF (@Size < 40)
  BEGIN
    ALTER TABLE [dbo].[Matters] ALTER COLUMN [YourRef] CHAR(40)
  END
GO

-- Add FixedFee to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'FixedFee'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD FixedFee char(1) NULL'

      EXEC (@command)

   END
GO

-- Add ShowCommentInAlarm to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'ShowCommentInAlarm'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD ShowCommentInAlarm char(1) DEFAULT(''N'')'

      EXEC (@command)

   END
GO

-- Add closed to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'Closed'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD Closed varchar(1) NULL'

      EXEC (@command)

            SET @command = 'Update [dbo].Matters'
      + char(13) + char(10) + '  set Closed = ''N'''

      EXEC (@command)

   END
GO

-- Add CloseDate to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'CloseDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD CloseDate datetime NULL'

      EXEC (@command)

   END
GO

-- Add OtherRef to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'OtherRef'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD OtherRef Varchar(10) NULL'

      EXEC (@command)

   END
GO

-- Add Location to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'Location'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD Location Varchar(20) NULL'

      EXEC (@command)

   END
GO

-- Add DestroyDate to the Matters Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Matters'
   AND SC.[name] = 'DestroyDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Matters'
      + char(13) + char(10) + '  ADD DestroyDate datetime NULL'

      EXEC (@command)

   END
GO
/*ALL MATTER INDEXES...*/

GO
/****** Object:  Index [CompBillingMatters]    Script Date: 08/20/2014 11:26:21 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'CompBillingMatters')
DROP INDEX [CompBillingMatters] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [CompBillingMatters]    Script Date: 08/20/2014 11:26:25 ******/
CREATE NONCLUSTERED INDEX [CompBillingMatters] ON [dbo].[matters] 
(
	[CompBillingMatter] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyBranch]    Script Date: 08/20/2014 11:26:44 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyBranch')
DROP INDEX [KeyBranch] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyBranch]    Script Date: 08/20/2014 11:26:51 ******/
CREATE NONCLUSTERED INDEX [KeyBranch] ON [dbo].[matters] 
(
	[Branch] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyClientMatter]    Script Date: 08/20/2014 11:27:20 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyClientMatter')
DROP INDEX [KeyClientMatter] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyClientMatter]    Script Date: 08/20/2014 11:27:26 ******/
CREATE NONCLUSTERED INDEX [KeyClientMatter] ON [dbo].[matters] 
(
	[ClientCode] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


GO
/****** Object:  Index [KeyCode]    Script Date: 08/20/2014 11:31:35 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyCode')
DROP INDEX [KeyCode] ON [dbo].[matters] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [KeyCode]    Script Date: 08/20/2014 11:31:40 ******/
CREATE NONCLUSTERED INDEX [KeyCode] ON [dbo].[matters] 
(
	[Code] ASC
)
INCLUDE ( [Description],
[Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyDept]    Script Date: 08/20/2014 11:31:57 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyDept')
DROP INDEX [KeyDept] ON [dbo].[matters] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [KeyDept]    Script Date: 08/20/2014 11:32:04 ******/
CREATE NONCLUSTERED INDEX [KeyDept] ON [dbo].[matters] 
(
	[Dept] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyFee]    Script Date: 08/20/2014 11:32:23 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyFee')
DROP INDEX [KeyFee] ON [dbo].[matters] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [KeyFee]    Script Date: 08/20/2014 11:32:33 ******/
CREATE NONCLUSTERED INDEX [KeyFee] ON [dbo].[matters] 
(
	[FECode] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyFePCode]    Script Date: 08/20/2014 11:32:51 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyFePCode')
DROP INDEX [KeyFePCode] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyFePCode]    Script Date: 08/20/2014 11:32:57 ******/
CREATE NONCLUSTERED INDEX [KeyFePCode] ON [dbo].[matters] 
(
	[PFECODE] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyFileColor]    Script Date: 08/20/2014 11:33:13 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyFileColor')
DROP INDEX [KeyFileColor] ON [dbo].[matters] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [KeyFileColor]    Script Date: 08/20/2014 11:33:26 ******/
CREATE NONCLUSTERED INDEX [KeyFileColor] ON [dbo].[matters] 
(
	[FileColour] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyFileNumber]    Script Date: 08/20/2014 11:33:48 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyFileNumber')
DROP INDEX [KeyFileNumber] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyFileNumber]    Script Date: 08/20/2014 11:33:57 ******/
CREATE NONCLUSTERED INDEX [KeyFileNumber] ON [dbo].[matters] 
(
	[FileNum] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyMatterInprogress]    Script Date: 08/20/2014 11:34:18 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyMatterInprogress')
DROP INDEX [KeyMatterInprogress] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyMatterInprogress]    Script Date: 08/20/2014 11:34:25 ******/
CREATE NONCLUSTERED INDEX [KeyMatterInprogress] ON [dbo].[matters] 
(
	[PCODE] ASC
)
INCLUDE ( [Code],
[Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyOldMatter]    Script Date: 08/20/2014 11:34:33 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyOldMatter')
DROP INDEX [KeyOldMatter] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyOldMatter]    Script Date: 08/20/2014 11:34:39 ******/
CREATE NONCLUSTERED INDEX [KeyOldMatter] ON [dbo].[matters] 
(
	[OldRef] ASC
)
INCLUDE ( [Code],
[Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyUser1]    Script Date: 08/20/2014 11:35:02 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyUser1')
DROP INDEX [KeyUser1] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyUser1]    Script Date: 08/20/2014 11:35:07 ******/
CREATE NONCLUSTERED INDEX [KeyUser1] ON [dbo].[matters] 
(
	[User1] ASC
)
INCLUDE ( [Code],
[Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyUser2]    Script Date: 08/20/2014 11:35:29 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyUser2')
DROP INDEX [KeyUser2] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyUser2]    Script Date: 08/20/2014 11:35:35 ******/
CREATE NONCLUSTERED INDEX [KeyUser2] ON [dbo].[matters] 
(
	[User2] ASC
)
INCLUDE ( [Code],
[Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyUser3]    Script Date: 08/20/2014 11:35:42 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyUser3')
DROP INDEX [KeyUser3] ON [dbo].[matters] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [KeyUser3]    Script Date: 08/20/2014 11:35:48 ******/
CREATE NONCLUSTERED INDEX [KeyUser3] ON [dbo].[matters] 
(
	[User3] ASC
)
INCLUDE ( [Code],
[Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KeyWorkType]    Script Date: 08/20/2014 11:35:55 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KeyWorkType')
DROP INDEX [KeyWorkType] ON [dbo].[matters] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [KeyWorkType]    Script Date: 08/20/2014 11:36:02 ******/
CREATE NONCLUSTERED INDEX [KeyWorkType] ON [dbo].[matters] 
(
	[WType] ASC,
	[Code] ASC
)
INCLUDE ( [Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [KYNET_index_Matters_On_Code_Permissions_ClientCode]    Script Date: 08/20/2014 11:36:10 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'KYNET_index_Matters_On_Code_Permissions_ClientCode')
DROP INDEX [KYNET_index_Matters_On_Code_Permissions_ClientCode] ON [dbo].[matters] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [KYNET_index_Matters_On_Code_Permissions_ClientCode]    Script Date: 08/20/2014 11:36:16 ******/
CREATE NONCLUSTERED INDEX [KYNET_index_Matters_On_Code_Permissions_ClientCode] ON [dbo].[matters] 
(
	[Code] ASC,
	[Permissions] ASC,
	[ClientCode] ASC
)
INCLUDE ( [Description],
[Closed],
[CloseDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
/****** Object:  Index [PK_matters]    Script Date: 08/20/2014 11:36:22 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[matters]') AND name = N'PK_matters')
ALTER TABLE [dbo].[matters] DROP CONSTRAINT [PK_matters]

GO
/****** Object:  Index [PK_matters]    Script Date: 08/20/2014 11:36:27 ******/
ALTER TABLE [dbo].[matters] ADD  CONSTRAINT [PK_matters] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]

/**/


-- Alter TimeDayBook Comment field to 1000 
DECLARE @Size INT

SELECT @Size = SC.[length]
  FROM [sys].[objects] SO
       INNER JOIN [sys].[syscolumns] SC
               ON SC.[id] = SO.[object_id]
              AND SC.[name] = 'Comment'
 WHERE SO.[name] = 'TimeDayBook'
   AND SO.[type] = 'U'

IF (@Size < 1000)
  BEGIN
    ALTER TABLE [dbo].[TimeDayBook] ALTER COLUMN [Comment] CHAR(1000)
  END
GO

-- Alter TimeEntry Comment field to 1000 
DECLARE @Size INT

SELECT @Size = SC.[length]
  FROM [sys].[objects] SO
       INNER JOIN [sys].[syscolumns] SC
               ON SC.[id] = SO.[object_id]
              AND SC.[name] = 'Comment'
 WHERE SO.[name] = 'TimeEntry'
   AND SO.[type] = 'U'

IF (@Size < 1000)
  BEGIN
    ALTER TABLE [dbo].[TimeEntry] ALTER COLUMN [Comment] CHAR(1000)
  END
GO


-- Alter BGDetail Narrative field to 1000 
DECLARE @Size INT

SELECT @Size = SC.[length]
  FROM [sys].[objects] SO
       INNER JOIN [sys].[syscolumns] SC
               ON SC.[id] = SO.[object_id]
              AND SC.[name] = 'Narrative'
 WHERE SO.[name] = 'BGDetail'
   AND SO.[type] = 'U'

IF (@Size < 1000)
  BEGIN
    ALTER TABLE [dbo].[BGDetail] ALTER COLUMN [Narrative] CHAR(1000)
  END
GO


IF NOT EXISTS(SELECT *
                FROM sys.objects SO
               WHERE so.[name] = 'SQLClosable')
BEGIN


CREATE TABLE [dbo].[SQLClosable](
	[AlreadyClosed] [int] NULL,
	[HasOutstandingBal] [int] NULL,
	[HasOutstandingTimeBal] [int] NULL,
	[HasOutstandingChargeBal] [int] NULL,
	[HasOutstandingRec] [int] NULL,
	[HasOutstandingDiary] [int] NULL,
	[HasOutstandingUndertakings] [int] NULL
) ON [PRIMARY]

END
GO

-- Add DisplayText to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'DisplayText'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD DisplayText nvarchar(200) NULL'

      EXEC (@command)

   END
GO

-- Add flag to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'Flag'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD Flag tinyint NULL'

      EXEC (@command)

   END
GO

-- Add ADDRESSTO to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'ADDRESSTO'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD ADDRESSTO nvarchar(2000) NULL'

      EXEC (@command)

   END
GO

-- Add CCTo to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'CCTo'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD CCTo nvarchar(2000) NULL'

      EXEC (@command)

   END
GO

-- Add BCCTo to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'BCCTo'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD BCCTo nvarchar(2000) NULL'

      EXEC (@command)

   END
GO

-- Add ClientContactID to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'ClientContactID'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD ClientContactID int NULL'

      EXEC (@command)

   END
GO

-- Add ContactCategory to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'ContactCategory'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD ContactCategory int NULL'

      EXEC (@command)

   END
GO

-- Add Campaign to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'Campaign'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD Campaign int NULL'

      EXEC (@command)

   END
GO

-- Add FileNum to the diary Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Diary'
   AND SC.[name] = 'FileNum'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Diary'
      + char(13) + char(10) + '  ADD FileNum int NULL'

      EXEC (@command)

   END
GO

-- Add RFBColour to the filecolours Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'FileColours'
   AND SC.[name] = 'RGBColour'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].FileColours'
      + char(13) + char(10) + '  ADD RGBColour int NULL'

      EXEC (@command)

   END
GO

-- Add FilePath to the DAIMXRef Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'DAIMXRef'
   AND SC.[name] = 'FilePath'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].DAIMXRef'
      + char(13) + char(10) + '  ADD FilePath varchar(255) NULL'

      EXEC (@command)

   END
GO

-- Add IMDOCID to the BriefDocuments Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'BriefDocuments'
   AND SC.[name] = 'IMDOCID'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].BriefDocuments'
      + char(13) + char(10) + '  ADD IMDOCID varchar(500) NULL'

      EXEC (@command)

   END
GO

-- Add DischargeDescription to the Undertakings Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Undertakings'
   AND SC.[name] = 'DischargeDescription'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Undertakings'
      + char(13) + char(10) + '  ADD DischargeDescription varchar(100) NULL'

      EXEC (@command)

   END
GO

-- Add EffectiveDate to the ClientChargeOutRates Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'ClientChargeOutRates'
   AND SC.[name] = 'EffectiveDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].ClientChargeOutRates'
      + char(13) + char(10) + '  ADD EffectiveDate datetime NULL'

      EXEC (@command)

   END
GO

-- Add AlarmStartupLaunch to the Control Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Control'
   AND SC.[name] = 'AlarmStartupLaunch'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Control'
      + char(13) + char(10) + '  ADD AlarmStartupLaunch nvarchar(1) NULL'

      EXEC (@command)

   END
GO

-- Add DocumentNamingMethod to the Control Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Control'
   AND SC.[name] = 'DocumentNamingMethod'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Control'
      + char(13) + char(10) + '  ADD DocumentNamingMethod nvarchar(1) NULL'

      EXEC (@command)

   END
GO

-- Add IsFileItemCodeAutoGen to the Control Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'Control'
   AND SC.[name] = 'IsFileItemCodeAutoGen'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].Control'
      + char(13) + char(10) + '  ADD IsFileItemCodeAutoGen int NULL'

      EXEC (@command)

   END
GO

-- Add EffectiveDate to the MatterChargeOutRates Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'MatterChargeOutRates'
   AND SC.[name] = 'EffectiveDate'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].MatterChargeOutRates'
      + char(13) + char(10) + '  ADD EffectiveDate datetime NULL'

      EXEC (@command)

   END
GO

-- Add ISLOCK to the SystemUserDefinedFields Table
DECLARE @Found int
DECLARE @command varchar(8000)

SELECT @Found = COUNT(1)
  FROM sysobjects SO
       INNER JOIN syscolumns SC
               ON SC.[id] = SO.[id]
 WHERE SO.[name] = 'SystemUserDefinedFields'
   AND SC.[name] = 'ISLOCK'

IF @Found = 0
   BEGIN
               SET @command = 'ALTER TABLE [dbo].SystemUserDefinedFields'
      + char(13) + char(10) + '  ADD ISLOCK tinyint NULL'

      EXEC (@command)

   END
GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'ControlViews'
      AND SO.[type] = 'U')
BEGIN
  CREATE
   TABLE [dbo].[ControlViews]
        ([id]                   INT IDENTITY(1, 1),
         [HANDLER]              VARCHAR(10),
         [View]                 VARCHAR(200),
         [ControlName]          VARCHAR(50),
         [OwnerName]            VARCHAR(50),
         [Default]              int,
         [ControlSettings]      NVARCHAR(MAX)
         CONSTRAINT [PK_ControlViews] PRIMARY KEY CLUSTERED
                   ([id]    ASC))
      ON [PRIMARY]
END
GO


/*Revnue stuff ... can be used for others in future*/
/****** Object:  Table [dbo].[ClientLink]    Script Date: 10/07/2014 11:22:53 ******/
IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'ClientLink'
      AND SO.[type] = 'U')
BEGIN
  CREATE TABLE [dbo].[ClientLink](
	[ClientCode] [char](10) NULL,
	[RecordID] [int] IDENTITY(1,1) NOT NULL,
	[ClientType] [char](1) NULL,
    CONSTRAINT [PK_ClientLink] PRIMARY KEY CLUSTERED 
	(
	[RecordID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

END
GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'MatterNETPrivileges'
      AND SO.[type] = 'U')
BEGIN
  CREATE
   TABLE [dbo].[MatterNETPrivileges]
        ([MATTER]          VARCHAR(20),
         [Privileges]      BIGINT
         CONSTRAINT [PK_MatterNETPrivileges] PRIMARY KEY CLUSTERED
                   ([MATTER] ASC))
      ON [PRIMARY]
END

GO

/*********************************************************
 pre-populate MatterNetPrivileges with the Permissions from
 the original matters table
 *********************************************************/
IF EXISTS
  (SELECT * 
     FROM [dbo].[matters] MAT
    WHERE ISNULL(MAT.[Permissions], 0) <> 0)
  BEGIN
    IF NOT EXISTS
      (SELECT * 
         FROM [dbo].[MatterNETPrivileges])
      BEGIN      
        INSERT
          INTO [dbo].[MatterNetPrivileges]
              ([MATTER],
               [Privileges])
        SELECT CONVERT(VARCHAR(20), RTRIM(ISNULL(MAT.[Code], ''))),
               CONVERT(BIGINT, MAT.[Permissions])
          FROM [dbo].[matters] MAT
               LEFT OUTER JOIN [dbo].[MatterNETPrivileges] MNP
                            ON MNP.[MATTER] = MAT.[Code]
         WHERE ISNULL(MAT.[permissions], 0) <> 0
           AND MNP.[MATTER] IS NULL
      END
  END
GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'HandlerNETLogons'
      AND SO.[type] = 'U')
BEGIN
  CREATE
   TABLE [dbo].[HandlerNETLogons]
        ([HANDLER]         VARCHAR(10),
         [Password]        VARCHAR(180),
         [Active]          VARCHAR(180),
         [Supervisor]      VARCHAR(180)
         CONSTRAINT [PK_HandlerNETLogons] PRIMARY KEY CLUSTERED
                   ([HANDLER] ASC))
      ON [PRIMARY]
END

GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'SecurityNETGroups'
      AND SO.[type] = 'U')
BEGIN
  CREATE
   TABLE [dbo].[SecurityNETGroups]
        ([GroupID]         INT,
         [BitWiseID]       BIGINT,
         [GroupName]       VARCHAR(200)
         CONSTRAINT [PK_SecurityNETGroups] PRIMARY KEY CLUSTERED
                   ([GroupID] ASC))
      ON [PRIMARY]
END

GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'HandlerNETGroupMembership'
      AND SO.[type] = 'U')
BEGIN
  CREATE
   TABLE [dbo].[HandlerNETGroupMembership]
        ([HANDLER]         VARCHAR(10),
         [BitWiseID]       BIGINT
         CONSTRAINT [PK_HandlerNETGroupMembership] PRIMARY KEY CLUSTERED
                   ([HANDLER] ASC))
      ON [PRIMARY]
END

GO

/******************************************************************
 *
 * Populate new tables from old tables - e.g. JGroups, DSSW21, etc
 * this will only happen if there is nothing in those tables yet.
 *
 ******************************************************************/
IF NOT EXISTS
  (SELECT *
     FROM [dbo].[SecurityNETGroups])
BEGIN
DECLARE @GroupID int
DECLARE @COMMAND VARCHAR(MAX)

SET @GroupID = 0
WHILE (@GroupID < 16)
  BEGIN
    SET @GroupID = @GroupID + 1

    SET @COMMAND = '
INSERT
  INTO [dbo].[SecurityNETGroups]
      ([GroupID],
       [BitWiseID],
       [GroupName])
SELECT OG.[GroupID],
       OG.[BitWiseID],
       OG.[GroupName]
  FROM (SELECT CONVERT(INT, ' + CONVERT(VARCHAR(2), @GroupID) + ') AS [GroupID],
               CONVERT(BIGINT, POWER(CONVERT(BIGINT, 2), CONVERT(BIGINT, ' + CONVERT(VARCHAR(2), @GroupID - 1) + '))) AS [BitWiseID],
               CONVERT(VARCHAR(200), RTRIM(ISNULL(JGRP_G' + CONVERT(VARCHAR(2), @GroupID) + ', ''''))) AS [GroupName]
          FROM [dbo].[JGroups]
         WHERE RTRIM(ISNULL(JGRP_G' + CONVERT(VARCHAR(2), @GroupID) + ', '''')) <> '''') OG
       LEFT OUTER JOIN [dbo].[SecurityNETGroups] SNG
                    ON SNG.[GroupID] = OG.[GroupID]
 WHERE SNG.[GroupID] IS NULL'

    EXEC (@COMMAND)
  END
END

GO

/*Fix Undertakings Discharge Date and Status*/

IF EXISTS
  (select SC.*
     from SYS.[objects] SO
          INNER JOIN SYS.[columns] SC
                  ON SC.[object_id] = SO.[object_id]
                 AND SC.[name] = 'DISCHARGEDATE'
                 AND SC.[system_type_id] <> 61
    where SO.[name] = 'Undertakings'
      and SO.[type] = 'U')
  BEGIN
    DECLARE @COMMAND VARCHAR(MAX)

    SET @COMMAND = 'ALTER TABLE [dbo].[Undertakings] ADD [DCD] DATETIME NULL'

    EXEC (@COMMAND)

    SET @COMMAND = 'UPDATE [dbo].[Undertakings] 
   SET [DCD] = CASE WHEN ISNULL([DISCHARGEDATE], 0) = 0 THEN NULL ELSE DATEADD(DD, [DISCHARGEDATE], CONVERT(DATETIME, ''18001228'')) END,
       [DISCHARGEDATE] = NULL'

    EXEC (@COMMAND)

    SET @COMMAND = 'ALTER TABLE [dbo].[Undertakings] ALTER COLUMN [DISCHARGEDATE] DATETIME NULL'

    EXEC (@COMMAND)

    SET @COMMAND = 'UPDATE [dbo].[Undertakings] SET [DISCHARGEDATE] = [DCD], [DCD] = NULL'

    EXEC (@COMMAND)

    SET @COMMAND = 'ALTER TABLE [dbo].[Undertakings] DROP COLUMN [DCD]'

    EXEC (@COMMAND)

  END
GO

IF EXISTS
  (select SC.*
     from SYS.[objects] SO
          INNER JOIN SYS.[columns] SC
                  ON SC.[object_id] = SO.[object_id]
                 AND SC.[name] = 'STATUS'
                 AND SC.[system_type_id] = 56
    where SO.[name] = 'Undertakings'
      and SO.[type] = 'U')
  BEGIN
    DECLARE @COMMAND VARCHAR(MAX)

    SET @COMMAND = 'SET IDENTITY_INSERT [dbo].[UndertakingStatus] ON
    
INSERT
  INTO [dbo].[UndertakingStatus]
      ([RECORDID],
       [STATUSDESC])
SELECT DISTINCT 
       UND.[Status],
       ''Unknown Status:'' + CONVERT(VARCHAR(10), UND.[Status])
  FROM [dbo].[Undertakings] UND
       LEFT OUTER JOIN [dbo].[UndertakingStatus] UNS
                    ON UNS.[RECORDID] = UND.[Status]
 WHERE ISNULL(UND.[Status], 0) <> 0
   AND UNS.[RECORDID] IS NULL
   
SET IDENTITY_INSERT [dbo].[UndertakingStatus] OFF
    
DECLARE @maxVal INT
SELECT @maxVal = ISNULL(max(RECORDID),0)+1 from [dbo].[UndertakingStatus]
DBCC CHECKIDENT(''UndertakingStatus'', RESEED, @maxVal)'

    EXEC (@COMMAND)

  END
GO

IF EXISTS
  (select SC.*
     from SYS.[objects] SO
          INNER JOIN SYS.[columns] SC
                  ON SC.[object_id] = SO.[object_id]
                 AND SC.[name] = 'STATUS'
                 AND SC.[system_type_id] <> 56
    where SO.[name] = 'Undertakings'
      and SO.[type] = 'U')
  BEGIN
    DECLARE @COMMAND VARCHAR(MAX)

    SET @COMMAND = 'ALTER TABLE [dbo].[Undertakings] ADD [DCS] INT NULL'

    EXEC (@COMMAND)

    SET @COMMAND = 'UPDATE UND
   SET UND.[DCS] = UNS.[RecordID],
       UND.[Status] = NULL
  FROM [dbo].[Undertakings] UND
       LEFT OUTER JOIN [dbo].[UndertakingStatus] UNS
                    ON UNS.[StatusDesc] = UND.[Status]'

    EXEC (@COMMAND)

    SET @COMMAND = 'ALTER TABLE [dbo].[Undertakings] ALTER COLUMN [STATUS] INT NULL'

    EXEC (@COMMAND)

    SET @COMMAND = 'UPDATE [dbo].[Undertakings] SET [STATUS] = [DCS], [DCS] = NULL'

    EXEC (@COMMAND)

    SET @COMMAND = 'ALTER TABLE [dbo].[Undertakings] DROP COLUMN [DCS]'

    EXEC (@COMMAND)
  END
GO