CREATE TABLE [dbo].[HARVERSIONS]
(
[VERSIONOBJID] [int] NOT NULL,
[ITEMOBJID] [int] NOT NULL,
[PACKAGEOBJID] [int] NOT NULL,
[PARENTVERSIONID] [int] NOT NULL,
[MERGEDVERSIONID] [int] NOT NULL,
[INBRANCH] [int] NOT NULL,
[MAPPEDVERSION] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VERSIONSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CREATIONTIME] [datetime] NOT NULL CONSTRAINT [DF__HARVERSIO__CREAT__2BC97F7C] DEFAULT (getutcdate()),
[CREATORID] [int] NOT NULL,
[MODIFIEDTIME] [datetime] NOT NULL CONSTRAINT [DF__HARVERSIO__MODIF__2CBDA3B5] DEFAULT (getutcdate()),
[MODIFIERID] [int] NOT NULL,
[DESCRIPTION] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VERSIONDATAOBJID] [int] NOT NULL,
[CLIENTMACHINE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CLIENTPATH] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ANCESTORVERSIONID] [int] NOT NULL CONSTRAINT [DF__HARVERSIO__ANCES__2DB1C7EE] DEFAULT ((0)),
[ITEMNAMEID] [int] NULL,
[ITEMTYPE] [int] NULL,
[PATHVERSIONID] [int] NULL,
[REFACTOR] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__HARVERSIO__REFAC__2EA5EC27] DEFAULT ('N'),
[REFACTOR_BY] [int] NULL CONSTRAINT [DF__HARVERSIO__REFAC__2F9A1060] DEFAULT ((0))
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO


   CREATE TRIGGER HARALLCHILDRENPATH_CVERID_FK ON dbo.HARVERSIONS FOR DELETE AS IF
   (@@ROWCOUNT = 0)
   RETURN IF EXISTS(SELECT *
   FROM HARALLCHILDRENPATH AS E           JOIN DELETED AS D
       ON E.CHILDVERSIONOBJID = D.VERSIONOBJID )    DELETE
   FROM HARALLCHILDRENPATH    
   FROM HARALLCHILDRENPATH AS E         JOIN DELETED AS D
       ON E.CHILDVERSIONOBJID = D.VERSIONOBJID  

GO
ALTER TABLE [dbo].[HARVERSIONS] ADD CONSTRAINT [HARVERSIONS_PK] PRIMARY KEY CLUSTERED ([VERSIONOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_NAMEITEM] ON [dbo].[HARVERSIONS] ([ITEMNAMEID], [ITEMOBJID], [VERSIONOBJID], [VERSIONSTATUS], [INBRANCH], [PATHVERSIONID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_ITEMOBJID] ON [dbo].[HARVERSIONS] ([ITEMOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_ITEMMAPPED] ON [dbo].[HARVERSIONS] ([ITEMOBJID], [MAPPEDVERSION], [VERSIONOBJID], [PACKAGEOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSION_NAMEIDPATHID_IDX] ON [dbo].[HARVERSIONS] ([PATHVERSIONID], [ITEMNAMEID]) INCLUDE ([ITEMOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_ITEM_IND] ON [dbo].[HARVERSIONS] ([ITEMOBJID], [VERSIONOBJID], [PATHVERSIONID], [REFACTOR_BY]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_VC] ON [dbo].[HARVERSIONS] ([ITEMOBJID], [VERSIONOBJID], [VERSIONSTATUS], [INBRANCH]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_VSTATUS] ON [dbo].[HARVERSIONS] ([ITEMOBJID], [VERSIONSTATUS]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_REFACTORBY] ON [dbo].[HARVERSIONS] ([REFACTOR_BY], [VERSIONSTATUS]) INCLUDE ([ITEMOBJID], [ITEMTYPE], [VERSIONOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_MERGED_IDX] ON [dbo].[HARVERSIONS] ([MERGEDVERSIONID], [VERSIONOBJID], [PACKAGEOBJID], [PARENTVERSIONID], [INBRANCH]) INCLUDE ([MAPPEDVERSION]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_STATUS] ON [dbo].[HARVERSIONS] ([VERSIONSTATUS], [ITEMOBJID], [VERSIONOBJID], [REFACTOR_BY]) INCLUDE ([ITEMTYPE], [PACKAGEOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_PKG_IND] ON [dbo].[HARVERSIONS] ([PACKAGEOBJID], [MODIFIERID], [CREATORID], [VERSIONSTATUS]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_PAR_IND] ON [dbo].[HARVERSIONS] ([PARENTVERSIONID], [VERSIONOBJID], [PACKAGEOBJID], [MERGEDVERSIONID], [INBRANCH], [MAPPEDVERSION]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_VERITEM] ON [dbo].[HARVERSIONS] ([VERSIONOBJID], [ITEMOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_INBRTYPSTAT] ON [dbo].[HARVERSIONS] ([INBRANCH], [ITEMTYPE], [VERSIONSTATUS]) INCLUDE ([ITEMOBJID], [MAPPEDVERSION], [MERGEDVERSIONID], [PACKAGEOBJID], [PARENTVERSIONID], [VERSIONOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSION_PKGBRANCHSTAT] ON [dbo].[HARVERSIONS] ([PACKAGEOBJID], [INBRANCH], [VERSIONSTATUS], [ITEMTYPE]) INCLUDE ([ITEMNAMEID], [ITEMOBJID], [PATHVERSIONID], [VERSIONOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARVERSIONS_DATA] ON [dbo].[HARVERSIONS] ([VERSIONDATAOBJID]) INCLUDE ([VERSIONOBJID]) ON [PRIMARY]
GO
CREATE STATISTICS [HARVERSIONS_MERGEDPARENT_STAT] ON [dbo].[HARVERSIONS] ([INBRANCH], [MERGEDVERSIONID], [PARENTVERSIONID], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_MERPAR_STAT] ON [dbo].[HARVERSIONS] ([INBRANCH], [MERGEDVERSIONID], [PACKAGEOBJID], [PARENTVERSIONID])
GO
CREATE STATISTICS [HARVERSIONS_NAMEVER_STAT] ON [dbo].[HARVERSIONS] ([ITEMNAMEID], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_PKGVERMER_STAT] ON [dbo].[HARVERSIONS] ([MERGEDVERSIONID], [PACKAGEOBJID], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_REFVERPATH_STAT] ON [dbo].[HARVERSIONS] ([PATHVERSIONID], [REFACTOR_BY], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_VERBRANCHPKGMERG_STAT] ON [dbo].[HARVERSIONS] ([INBRANCH], [MERGEDVERSIONID], [PACKAGEOBJID], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_VERMAP_STAT] ON [dbo].[HARVERSIONS] ([MAPPEDVERSION], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_VERPATHITEMN_STAT] ON [dbo].[HARVERSIONS] ([ITEMNAMEID], [PATHVERSIONID], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_VERPATHITEMREF_STAT] ON [dbo].[HARVERSIONS] ([ITEMOBJID], [PATHVERSIONID], [REFACTOR_BY], [VERSIONOBJID])
GO
CREATE STATISTICS [HARVERSIONS_VERPITEMITEMNPATH_STAT] ON [dbo].[HARVERSIONS] ([ITEMNAMEID], [ITEMOBJID], [PATHVERSIONID], [VERSIONOBJID])
GO
ALTER TABLE [dbo].[HARVERSIONS] ADD CONSTRAINT [HARVERSION_NAMEID_FK] FOREIGN KEY ([ITEMNAMEID]) REFERENCES [dbo].[HARITEMNAME] ([NAMEOBJID])
GO
ALTER TABLE [dbo].[HARVERSIONS] ADD CONSTRAINT [HARVERSIONS_PATHVERID_FK] FOREIGN KEY ([PATHVERSIONID]) REFERENCES [dbo].[HARVERSIONS] ([VERSIONOBJID])
GO
ALTER TABLE [dbo].[HARVERSIONS] ADD CONSTRAINT [HARVERSIONS_PKGID_FK] FOREIGN KEY ([PACKAGEOBJID]) REFERENCES [dbo].[HARPACKAGE] ([PACKAGEOBJID])
GO
EXEC sp_addextendedproperty N'Caption', N'The HARVERSIONS table stores the version object. ', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', NULL, NULL
GO
EXEC sp_addextendedproperty N'Caption', N'Ancestor version object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'ANCESTORVERSIONID'
GO
EXEC sp_addextendedproperty N'Caption', N'Computer name that the version was checked in from or checked out to', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'CLIENTMACHINE'
GO
EXEC sp_addextendedproperty N'Caption', N'Path name that the version was checked in from or checked out to', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'CLIENTPATH'
GO
EXEC sp_addextendedproperty N'Caption', N'Time object was created', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'CREATIONTIME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of creator', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'CREATORID'
GO
EXEC sp_addextendedproperty N'Caption', N'User-defined description', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'DESCRIPTION'
GO
EXEC sp_addextendedproperty N'Caption', N'Branch object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'INBRANCH'
GO
EXEC sp_addextendedproperty N'Caption', N'Item name version object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'ITEMNAMEID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of item', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'ITEMOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Type of item', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'ITEMTYPE'
GO
EXEC sp_addextendedproperty N'Caption', N'Mapped version number', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'MAPPEDVERSION'
GO
EXEC sp_addextendedproperty N'Caption', N'Merged version object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'MERGEDVERSIONID'
GO
EXEC sp_addextendedproperty N'Caption', N'Time object was modified', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'MODIFIEDTIME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of modifier', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'MODIFIERID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of package', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'PACKAGEOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Parent version object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'PARENTVERSIONID'
GO
EXEC sp_addextendedproperty N'Caption', N'Path version object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'PATHVERSIONID'
GO
EXEC sp_addextendedproperty N'Caption', N'Marks this version for refactoring (N)o (D)eleted (V)moved (A)renamed', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'REFACTOR'
GO
EXEC sp_addextendedproperty N'Caption', N'Points to the original refactored path version', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'REFACTOR_BY'
GO
EXEC sp_addextendedproperty N'Caption', N'Version data object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'VERSIONDATAOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Version object ID', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'VERSIONOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Version status (R)eserved (D)eleted (N)ormal (M)erge', 'SCHEMA', N'dbo', 'TABLE', N'HARVERSIONS', 'COLUMN', N'VERSIONSTATUS'
GO