CREATE TABLE [dbo].[HARITEMS]
(
[ITEMOBJID] [int] NOT NULL,
[ITEMNAME] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ITEMNAMEUPPER] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ITEMTYPE] [int] NOT NULL,
[PARENTOBJID] [int] NOT NULL,
[REPOSITOBJID] [int] NOT NULL,
[CREATIONTIME] [datetime] NOT NULL CONSTRAINT [DF__HARITEMS__CREATI__70DDC3D8] DEFAULT (getutcdate()),
[CREATORID] [int] NOT NULL,
[MODIFIEDTIME] [datetime] NOT NULL CONSTRAINT [DF__HARITEMS__MODIFI__71D1E811] DEFAULT (getutcdate()),
[MODIFIERID] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

   CREATE TRIGGER HARITEMS_PID_TRIGGER ON dbo.HARITEMS FOR DELETE AS IF
   (@@ROWCOUNT = 0)
   RETURN IF EXISTS(SELECT *
   FROM HARITEMS AS E JOIN DELETED   AS D ON E.PARENTOBJID = D.ITEMOBJID ) DELETE
   FROM HARITEMS
   FROM HARITEMS AS E JOIN DELETED   AS D ON E.PARENTOBJID = D.ITEMOBJID  

GO

   CREATE TRIGGER HARVERSIONS_ITEMID_TRIGGER ON dbo.HARITEMS FOR DELETE AS IF
   (@@ROWCOUNT = 0)
   RETURN IF EXISTS(SELECT *
   FROM HARVERSIONS AS E           JOIN DELETED AS D
       ON E.ITEMOBJID = D.ITEMOBJID )    DELETE
   FROM HARVERSIONS    
   FROM HARVERSIONS AS E         JOIN DELETED AS D
       ON E.ITEMOBJID = D.ITEMOBJID  

GO
ALTER TABLE [dbo].[HARITEMS] ADD CONSTRAINT [HARITEMS_PK] PRIMARY KEY CLUSTERED ([ITEMOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_ITEMNAME] ON [dbo].[HARITEMS] ([ITEMNAME]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_ITEMNAMEUPPER] ON [dbo].[HARITEMS] ([ITEMNAMEUPPER], [PARENTOBJID], [ITEMOBJID], [ITEMNAME]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_REPOSITOBJID] ON [dbo].[HARITEMS] ([REPOSITOBJID], [ITEMOBJID]) INCLUDE ([CREATIONTIME], [CREATORID], [ITEMTYPE], [MODIFIEDTIME], [MODIFIERID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_IND_TYPE] ON [dbo].[HARITEMS] ([ITEMTYPE], [PARENTOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_PID_FK] ON [dbo].[HARITEMS] ([PARENTOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_PARENTTYPE] ON [dbo].[HARITEMS] ([PARENTOBJID], [ITEMTYPE], [ITEMOBJID], [MODIFIERID], [CREATORID], [ITEMNAME], [REPOSITOBJID], [CREATIONTIME], [MODIFIEDTIME]) ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'Caption', N'The HARITEMS table stores the definition of an object of an item. ', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', NULL, NULL
GO
EXEC sp_addextendedproperty N'Caption', N'Time object was created', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'CREATIONTIME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of creator', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'CREATORID'
GO
EXEC sp_addextendedproperty N'Caption', N'Name of item', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'ITEMNAME'
GO
EXEC sp_addextendedproperty N'Caption', N'Name of item in uppercase characters', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'ITEMNAMEUPPER'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of item', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'ITEMOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Type of item: 0 path/1 file', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'ITEMTYPE'
GO
EXEC sp_addextendedproperty N'Caption', N'Time object was modified', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'MODIFIEDTIME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of modifier', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'MODIFIERID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of the path the item is stored under', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'PARENTOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of repository', 'SCHEMA', N'dbo', 'TABLE', N'HARITEMS', 'COLUMN', N'REPOSITOBJID'
GO