-- This script will update Insight Collection server v3.1 to version v4.1--
-- Please Make a backup copy of your database before applying this script.--

create table DTEntityToEntityMap(
  EntityTypeID    int not null,
  EntityID        int not null,
  RelEntityTypeID int not null,
  RelEntityID     int not null,

  constraint PK_DTEntityToEntityMap primary key(EntityTypeID,EntityID,RelEntityTypeID,RelEntityID)
)
go


alter table DTHierarchyNodes drop
  PK_DTHierarchyNodes
go

alter table DTHierarchyNodes add
  IsLocalVocabularyNode int
go

alter table DTHierarchyNodes with nocheck add
  constraint PK_DTHierarchyNodes primary key(HierarchyID,HierarchyIndex)
go




create table DTTermToEntityMap(
  TermID       int not null,
  EntityTypeID int not null,
  EntityID     int not null,
  FieldID      int not null,
  Grouping     int not null,

  constraint PK_DTTermToEntityMap primary key(TermID,EntityTypeID,EntityID,FieldID,Grouping)
)
go


create table DTValueToEntityMap(
  ValueID            int          not null,
  EntityTypeID       int          not null,
  EntityID           int          not null,
  RelEntityTypeID    int          not null,
  RelEntityID        int          not null,
  ParentEntityTypeID int          not null,
  ParentEntityID     int          not null,
  Preferred          int,
  DisplayOrder       int,
  Grouping           nvarchar(50),

  constraint PK_DTValueToEntity primary key(ValueID,EntityTypeID,EntityID,RelEntityTypeID,RelEntityID,ParentEntityTypeID,ParentEntityID)
)
go



alter table DTValues add
  ValueDate_Start int,
  ValueDate_End int
go






create table IRApprovalLevels(
  ApprovalLevel        int not null constraint PK_IRApprovalLevels primary key,
  CommitToInvertedData int default (0),
  CommitToSourceData   int default (0)
)
go



create table IREntityChanges(
  ChangeID                int            not null constraint PK_IREntityChanges primary key,
  ChangeCategory          int,
  ChangeType              int,
  ChangeStatus            int,
  ApprovalLevel           int,
  CommittedToInvertedData int            default (0),
  CommittedToSourceData   int            default (0),
  EntityTypeID            int,
  EntityID                int,
  UserID                  int,
  ChangeTimeStamp         nvarchar(50),
  LastEditorUserID        int            default (0),
  LastEditorTimeStamp     nvarchar(50),
  FieldID                 int,
  ValueChange             nvarchar(4000),
  RelEntityTypeID         int,
  RelEntityID             int,
  MediaID                 int,
  MetaData                nvarchar(255)
)
go

create table IREntityLocks(
  EntityTypeID  int          not null,
  EntityID      int          not null,
  UserID        int,
  IsLocked      int,
  LockTimeStamp nvarchar(50),

  constraint PK_IREntityLocks primary key(EntityTypeID,EntityID)
)
go

create table IREntityMediaMap(
  EntityTypeID int not null default (1),
  EntityID     int not null,
  MediaID      int not null,

  constraint PK_IREntityMediaMap primary key(EntityTypeID,EntityID,MediaID)
)
go



alter table IRFields alter column
  JoinPath int
go

alter table IRFields alter column
  DelimiterType int
go





create table IRImageToDocumentMap(
  ImageID    [decimal](18,0) not null,
  DocumentID int not null,

  constraint PK_IRImageToDocumentMap primary key(ImageID,DocumentID) with fillfactor=90
)
go



create table IRLinkFields(
  FieldID          int          not null constraint PK_IRLinkFields primary key nonclustered,
  FieldName        nvarchar(50),
  FieldDisplayName nvarchar(50),
  DisplayOrder     int,
  IsDate           int
)
go

create table IRLinkRepository(
  LinkID          int,
  LinkCode        nvarchar(1000),
  LinkTimeStamp   nvarchar(255),
  LinkRegion      nvarchar(255),
  ImageID         [decimal](18,0),
  ObjectID        [decimal](18,0),
  Author          nvarchar(255),
  Subject         nvarchar(255),
  Category        nvarchar(255),
  Organization    nvarchar(255),
  Email           nvarchar(255),
  Username        nvarchar(255),
  DateStamp       nvarchar(255),
  DateStamp_Start int,
  DateStamp_End   int,
  AnnotationText  nvarchar(2000),
  WebAddress      nvarchar(2000)
)
go



alter table IRObjectDimension drop
  PK_IRObjectDimension
go

alter table IRObjectDimension add
  WidthQualifierFieldID int,
  WidthQualifierValue nvarchar(50),
  HeightQualifierFieldID int,
  HeightQualifierValue nvarchar(50),
  WidthMetricFieldID int,
  HeightMetricFieldID int,
  MetricNameMap nvarchar(100)
go

alter table IRObjectDimension with nocheck add
  constraint PK_IRObjectDimension primary key(WidthFieldID,HeightFieldID,Metric)
go


alter table IRProfile drop
  PK_IRProfile
go

alter table IRProfile add
  ApprovalLevel int,
  MEDEPermissions int
go



alter table IRSPS drop
  PK_IRSPS
go

alter table IRSPS alter column
  URL nvarchar(255)
go

alter table IRSPS with nocheck add
  constraint PK_IRSPS primary key(SPSNum,ResolutionSize,MediaType,CollectionId,Format)
go



alter table IRUserGroups drop
  PK_IRUserGroups,
  column StartIPAddress,
  column EndIPAddress
go

alter table IRUserGroups with nocheck add
  constraint PK_IRUserGroups primary key(GroupID)
go

create table IRUsers(
  UserID             int          not null constraint PK_IRUsers primary key,
  LoginName          nvarchar(50),
  FullName           nvarchar(50),
  Organization       nvarchar(50),
  Email              nvarchar(50),
  Phone              nvarchar(50),
  Address            nvarchar(50),
  LastVisitTimeStamp nvarchar(50)
)
go



create table ISDocumentDescriptions(
  DocumentID int          not null,
  LevelID    int          not null,
  LevelValue int          not null,
  LevelTerm  nvarchar(50),

  constraint PK_ISDocumentLevelDescriptions primary key(DocumentID,LevelID,LevelValue) with fillfactor=90
)
go

create table ISDocumentLevelDescriptions(
  DocumentID int          not null,
  LevelID    int          not null,
  LevelValue int          not null,
  LevelTerm  nvarchar(50)
)
go

create table ISDocumentPages(
  PageID       int not null identity constraint PK_ISDocumentPages primary key,
  DocumentID   int not null,
  DisplayOrder int,
  ObjectID     [decimal](18,0) not null,
  ImageID      [decimal](18,0) not null,
  LinkCount    int,
  Level1       int,
  Level2       int,
  Level3       int,
  Level4       int,
  Level5       int,
  Level6       int,
  Level7       int
)
go

create table ISDocuments(
  DocumentID         int           not null constraint PK_ISDocuments primary key,
  DocumentName       nvarchar(255),
  LinkVisibilityMode int,
  GenerateDocument   int,
  IdentityFieldID    int,
  IdentityValue      nvarchar(255),
  TargetGroupID      int,
  MaxLevelCount      int,
  LevelName1         nvarchar(50),
  LevelFieldID1      int,
  LevelName2         nvarchar(50),
  LevelFieldID2      int,
  LevelName3         nvarchar(50),
  LevelFieldID3      int,
  LevelName4         nvarchar(50),
  LevelFieldID4      int,
  LevelName5         nvarchar(50),
  LevelFieldID5      int,
  LevelName6         nvarchar(50),
  LevelFieldID6      int,
  LevelName7         nvarchar(50),
  LevelFieldID7      int
)
go

create table ISEntityFields(
  EntityTypeID              int          not null,
  FieldID                   int          not null,
  JoinID                    int,
  FieldTableID              int,
  FieldValueColumnName      nvarchar(50),
  PreferredValueColumnName  nvarchar(50),
  RestrictionLevel          int,
  IsRequired                int,
  IntegerRange              nvarchar(50),
  IsValueListAllowed        int,
  MinApprovalToAddNewValue  int,
  RepeatValuesAllowed       int,
  DisplayInRelatedEntities  int,
  AlwaysValidateNewValues   int,
  HierarchyIDColumnName     nvarchar(50),
  HierarchyNodeIDColumnName nvarchar(50),

  constraint PK_ISEntityTypeFields primary key(EntityTypeID,FieldID)
)
go

create table ISEntityRelationships(
  EntityTypeID     int not null,
  RelEntityTypeID  int not null,
  JoinID           int,
  SourceMapTableID int,
  PublishRelatedMedia int NULL,
  RelatedEntitiesAllowed int NULL,

  constraint PK_ISEntityTypeRelationships primary key(EntityTypeID,RelEntityTypeID)
)
go


create table ISEntityTypes(
  EntityTypeID          int          not null constraint PK_ISEntityTypes primary key,
  EntityTypeName        nvarchar(50),
  EntityTypeDisplayName nvarchar(50),
  EntityTableID         int,
  RestrictionLevel      int,
  EntityIDColumnName    nvarchar(50),
  MaxEntityID           int,
  IsVocabulary          int,
  IsDependent           int,
  IsDupeCheckEnabled    int,
  IsRefCheckEnabled     int,
  IsPreferredEntity	int,
  EntityTypeDisplayOrder	int
)
go

alter table ISHierarchies drop
  PK_HierarchyID
go

alter table ISHierarchies alter column
  IsANSICompliant int not null
go

alter table ISHierarchies alter column
  Generate int not null
go


alter table ISTables alter column
  Hierarchy int
go

alter table ISTables drop
  column Flattened
go



create table ITEntityToEntityMap(
  EntityTypeID    int not null,
  EntityID        int not null,
  RelEntityTypeID int not null,
  RelEntityID     int not null,

  constraint PK_ITEntityToEntityMap primary key(EntityTypeID,EntityID,RelEntityTypeID,RelEntityID)
)
go



alter table ITHierarchyNodes drop
  PK_ITHierarchyNodes
go

alter table ITHierarchyNodes add
  IsLocalVocabularyNode int
go

alter table ITHierarchyNodes with nocheck add
  constraint PK_ITHierarchyNodes primary key(HierarchyID,HierarchyIndex)
go



create table ITTermToEntityMap(
  TermID       int not null,
  EntityTypeID int not null,
  EntityID     int not null,
  FieldID      int not null,
  Grouping     int not null,

  constraint PK_ITTermToEntityMap primary key(TermID,EntityTypeID,EntityID,FieldID,Grouping)
)
go



create table ITValueToEntityMap(
  ValueID            int          not null,
  EntityTypeID       int          not null,
  EntityID           int          not null,
  RelEntityTypeID    int          not null,
  RelEntityID        int          not null,
  ParentEntityTypeID int          not null,
  ParentEntityID     int          not null,
  Preferred          int,
  DisplayOrder       int,
  Grouping           nvarchar(50),

  constraint PK_ITValueToEntityMap primary key(ValueID,EntityTypeID,EntityID,RelEntityTypeID,RelEntityID,ParentEntityTypeID,ParentEntityID)
)
go



alter table ITValues add
  ValueDate_Start int,
  ValueDate_End int
go



alter table SLStandardFields alter column
  SelectListBehavior int
go




create table lexicon(
  thesauri_id             int          not null,
  lexicon_id              int          not null,
  term                    nvarchar(80),
  node_type               nvarchar(10),
  differentiator          nvarchar(40),
  access_code             int,
  valid_catalog_term_flag int,

  constraint PK_lexicon primary key(thesauri_id,lexicon_id)
)
go

create table lexicon_term_relation(
  thesauri_id       int         not null,
  lexicon_id        int         not null,
  relation_type     nvarchar(4) not null,
  relatedlexicon_id int         not null,

  constraint PK_lexicon_term_relation primary key(thesauri_id,lexicon_id,relation_type,relatedlexicon_id)
)
go



ALTER TABLE ISJoins ADD
	JoinType int NULL
go
	
ALTER TABLE ISJoins ADD CONSTRAINT
	DF_ISJoins_JoinType DEFAULT 1 FOR JoinType
go

ALTER TABLE IRVersion ADD
	IsInscribeCompliant int NULL
go





--Set defaults




ALTER TABLE [dbo].[IRFIELDS] WITH NOCHECK ADD
   	SELECTLISTBEHAVIOR int NULL
GO

ALTER TABLE [dbo].[CCMEDIABATCHES] WITH NOCHECK ADD
   	STARTINGMEDIAID int NULL
GO


alter table IRCollectionConfigurationInfo add
  RemoteLaunchBaseURL nvarchar(255),
  RemoteLaunchHyperlinkTemplate nvarchar(1000),
  IsPersonalCollection int NULL,
  PcOwnerID int NULL,
  PcOwnerUserGroupID int NULL,
  PcStandardName nvarchar(255) NULL,
  PcUserGroupMemberPrivs int NULL,
  PcNonUserGroupMemberPrivs int NULL,
  IsPcMediaShared int NULL
GO

ALTER TABLE SLSTANDARDSLOOKUP ADD ISTEMPLATE int NULL, STANDARDCREATORID int NULL
GO

alter table IRUserGroups add BrowserIPAddressRanges nvarchar(1000)
GO

alter table IRMaxIDs add MaxLpsID int
GO

alter table IRProfile add 
ExportPresentationPriv int, EditPersonalCollections int
GO

UPDATE IRMaxIDs SET MaxLpsID=(SELECT (MAX(LPSid)+1) AS MaxLpsID FROM  IRLPS)
GO

--(optional)--UPDATE IRMaxIDs SET MaxMediaID=(SELECT (MAX(Imageid)+10000000) AS MaxMediaID FROM  IRImagefiles) GO

UPDATE IRVersion SET Version='4'
GO

UPDATE IRVersion SET IsInscribeCompliant=0 WHERE IsInscribeCompliant IS NULL OR IsInscribeCompliant=''
GO

UPDATE IRFields SET SELECTLISTBEHAVIOR=1 WHERE SELECTLISTBEHAVIOR IS NULL OR SELECTLISTBEHAVIOR=''
GO

UPDATE SLStandardFields SET SELECTLISTBEHAVIOR=1 WHERE SELECTLISTBEHAVIOR IS NULL OR SELECTLISTBEHAVIOR=''
GO

UPDATE IRUserGroups SET BrowserIPAddressRanges='0.0.0.0-255.255.255.255' WHERE BrowserIPAddressRanges IS NULL OR BrowserIPAddressRanges=''
GO

UPDATE IRProfile SET ExportPresentationPriv=8 WHERE ExportPresentationPriv is null
GO

UPDATE ISEntityRelationships SET RelatedEntitiesAllowed=0 WHERE RelatedEntitiesAllowed IS NULL
GO

UPDATE ISEntityRelationships SET PublishRelatedMedia=0 WHERE PublishRelatedMedia IS NULL
GO

UPDATE ISEntityTypes SET IsPreferredEntity=0 WHERE IsPreferredEntity IS NULL
GO

UPDATE IRCollectionConfigurationInfo SET HelpGroup='http://www.lunaimaging.com/v4_0help/insight/insight4.htm' WHERE HelpGroup like '%help-group.html' OR HelpGroup = '' OR HelpGroup  IS NULL
GO

UPDATE IRCollectionConfigurationInfo SET HelpImage='http://www.lunaimaging.com/v4_0help/insight/insight4.htm' WHERE HelpImage like '%help-image.html' OR HelpImage = '' OR HelpImage  IS NULL
GO

INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (1,'Author','author',0,0) 
go 
 INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (2,'Subject','subject',0,0) 
go 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (3,'Category','category',0,0) 
go 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (4,'Organization','organization',0,0) 
go 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (5,'Email','email',0,0) 
go 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (6,'Username','user name',0,0) 
go 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (7,'DateStamp','date stamp',0,1) 
go 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (8,'AnnotationText','annotation text',0,0) 
go 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (9,'WebAddress','web address',0,0) 
go 



-------------------------Remove Indexes and Primary key for IRGroupImageMap------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'IRGroupImageMap'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for IRGroupImageMap------------
ALTER TABLE dbo.IRGroupImageMap
	DROP CONSTRAINT DF_IRGroupImageMap_MpdSeries
go

ALTER TABLE IRGroupImageMap
	DROP COLUMN MpdSeries
go


ALTER TABLE IRGroupImageMap ADD
	MpdSeries int NOT NULL CONSTRAINT DF_IRGroupImageMap_MpdSeries DEFAULT 0
go
ALTER TABLE IRGroupImageMap ADD CONSTRAINT
	PK_IRGroupImageMap PRIMARY KEY CLUSTERED 
	(
	GroupID,
	ImageID,
	MviSeries,
	MpdSeries
	) ON [PRIMARY]
go

ALTER TABLE IRGroupImageMap ADD CONSTRAINT
	DF_IRGroupImageMap_GroupID DEFAULT 1 FOR GroupID

go
ALTER TABLE IRGroupImageMap ADD CONSTRAINT
	DF_IRGroupImageMap_MviSeries DEFAULT 0 FOR MviSeries

go

-------------------------End Changes for IRGroupImageMap----------------------------

-------------------------Remove Indexes and Primary key for IRLINKREPOSITORY------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'IRLINKREPOSITORY'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for IRLINKREPOSITORY------------
alter table IRLINKREPOSITORY DROP COLUMN  LINKID;
alter table IRLINKREPOSITORY ADD  LINKID int NULL;

-------------------------End Changes for IRLINKREPOSITORY----------------------------
go

-------------------------Remove Indexes and Primary key for ISDocumentPages------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'ISDocumentPages'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for ISDocumentPages------------
alter table ISDocumentPages DROP COLUMN  PageID;
alter table ISDocumentPages ADD  PageID int NULL;

-------------------------End Changes for ISDocumentPages----------------------------
go
-------------------------Remove Indexes and Primary key for IRImageFiles------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'IRImageFiles'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for IRImageFiles------------

ALTER TABLE IRIMAGEFILES ADD CONSTRAINT PK_IRIMAGEFILES
    PRIMARY KEY (IMAGEID, RESOLUTIONSIZE, FORMAT, MEDIATYPE);

CREATE INDEX IDX_IRIMGEFLS_IMIDLPSFMTRESMED 
ON IRIMAGEFILES (IMAGEID, LPSID, FORMAT, RESOLUTIONSIZE, MEDIATYPE)
; 

CREATE INDEX IDX_IRIMGEFLS_FMTRESMEDLPSIMD 
ON IRIMAGEFILES (FORMAT, RESOLUTIONSIZE, MEDIATYPE, LPSID, IMAGEID, WIDTH, HEIGHT, FILENAME)
; 

-------------------------End Changes for IRImageFiles----------------------------
go
-------------------------Remove Indexes and Primary key for DTFLATOBJECTDATA------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTFLATOBJECTDATA'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for DTFLATOBJECTDATA------------

ALTER TABLE DTFLATOBJECTDATA ADD CONSTRAINT PK_DTFLATOBJECTDATA
    PRIMARY KEY (OBJECTID);

CREATE UNIQUE INDEX IDX__DTFLAT_OBJSRTTHUMB1234 
ON DTFLATOBJECTDATA (OBJECTID, SORTORDER, THUMBDATA1, THUMBDATA2, THUMBDATA3, THUMBDATA4)
; 

CREATE UNIQUE INDEX IDX_DTFLAT_SORTORDER
ON DTFLATOBJECTDATA (SORTORDER)
; 

CREATE UNIQUE INDEX IDX_DTFLAT_SORTOBJID 
ON DTFLATOBJECTDATA (SORTORDER, OBJECTID)
; 

-------------------------End Changes for DTFLATOBJECTDATA----------------------------
go
-------------------------Remove Indexes and Primary key for DTTERMOBJECTMAP------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTTERMOBJECTMAP'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF-------------------------Remove Indexes and Primary key for IRImageFiles------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'IRImageFiles'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for IRImageFiles------------

ALTER TABLE IRIMAGEFILES ADD CONSTRAINT PK_IRIMAGEFILES
    PRIMARY KEY (IMAGEID, RESOLUTIONSIZE, FORMAT, MEDIATYPE);

CREATE INDEX IDX_IRIMGEFLS_IMIDLPSFMTRESMED 
ON IRIMAGEFILES (IMAGEID, LPSID, FORMAT, RESOLUTIONSIZE, MEDIATYPE)
; 

CREATE INDEX IDX_IRIMGEFLS_FMTRESMEDLPSIMD 
ON IRIMAGEFILES (FORMAT, RESOLUTIONSIZE, MEDIATYPE, LPSID, IMAGEID, WIDTH, HEIGHT, FILENAME)
; 

-------------------------End Changes for IRImageFiles----------------------------
go
-------------------------Remove Indexes and Primary key for DTFLATOBJECTDATA------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTFLATOBJECTDATA'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for DTFLATOBJECTDATA------------

ALTER TABLE DTFLATOBJECTDATA ADD CONSTRAINT PK_DTFLATOBJECTDATA
    PRIMARY KEY (OBJECTID);

CREATE UNIQUE INDEX IDX__DTFLAT_OBJSRTTHUMB1234 
ON DTFLATOBJECTDATA (OBJECTID, SORTORDER, THUMBDATA1, THUMBDATA2, THUMBDATA3, THUMBDATA4)
; 

CREATE UNIQUE INDEX IDX_DTFLAT_SORTORDER
ON DTFLATOBJECTDATA (SORTORDER)
; 

CREATE UNIQUE INDEX IDX_DTFLAT_SORTOBJID 
ON DTFLATOBJECTDATA (SORTORDER, OBJECTID)
; 

-------------------------End Changes for DTFLATOBJECTDATA----------------------------
go
-------------------------Remove Indexes and Primary key for DTTERMOBJECTMAP------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTTERMOBJECTMAP'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for DTTERMOBJECTMAP------------


CREATE INDEX IDX_DTTRMOBJMAP_TERMIDOBJID 
ON DTTERMOBJECTMAP (TERMID, OBJECTID)
; 

CREATE INDEX IDX_DTTRMOBJMAP_FLDIDTERMID 
ON DTTERMOBJECTMAP (FIELDID, TERMID)
; 


-------------------------End Changes for DTTERMOBJECTMAP---------------------------- 
go
-------------------------Remove Indexes and Primary key for DTVALUES------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTVALUES'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 
-------------------------Add Indexes and Primary key for DTVALUES------------

CREATE INDEX IDX_DTVAL_VALIDFLDIDENBLD
ON DTVALUES (VALUEID, FIELDID, ENABLED)
; 

--CREATE INDEX IDX_DTVAL_VALIDVALTXT 
--ON DTVALUES (VALUEID, VALUETEXT)
--; 

CREATE INDEX IDX_DTVAL_FLDIDVALID 
ON DTVALUES (FIELDID, VALUEID)
; 


--CREATE INDEX IDX_DTVAL_VALTXT
--ON DTVALUES (VALUETEXT)
--; 

-------------------------End Changes for DTVALUES----------------------------
go

-------------------------Remove Indexes and Primary key for DTVALUETOOBJECT------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTVALUETOOBJECT'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 
-------------------------Add Indexes and Primary key for DTVALUETOOBJECT------------

ALTER TABLE DTVALUETOOBJECT ADD CONSTRAINT PK_DTVALUETOOBJECT
    PRIMARY KEY (VALUEID, OBJECTID, GROUPING);

CREATE INDEX IDX_DTVALTOOBJ_OBJID 
ON DTVALUETOOBJECT (OBJECTID)
; 

CREATE INDEX IDX_DTVALTOOBJ_PFRDOBJIDVALID 
ON DTVALUETOOBJECT (PREFERRED, OBJECTID, VALUEID)
; 

-------------------------End Changes for DTVALUETOOBJECT----------------------------
go
-------------------------Remove Indexes and Primary key for IRFIELDS------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'IRFIELDS'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for IRFIELDS------------
ALTER TABLE IRFIELDS ADD CONSTRAINT PK_IRFIELDS
    PRIMARY KEY (FIELDID);
    
CREATE INDEX IDX_IRFLDS_FLDIDDSPLODR 
ON IRFIELDS (FIELDTYPE, DISPLAYORDER)
; 

CREATE INDEX IDX_IRFLDS_FLDIDDLDTYP 
ON IRFIELDS (FIELDID, FIELDTYPE)
; 

-------------------------End Changes for IRFIELDS----------------------------

go
-------------------------Remove Indexes and Primary key for IROBJECTIMAGEMAP------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'IROBJECTIMAGEMAP'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 
-------------------------Add Indexes and Primary key for IROBJECTIMAGEMAP------------

ALTER TABLE IROBJECTIMAGEMAP ADD CONSTRAINT PK_IROBJECTIMAGEMAP
    PRIMARY KEY (OBJECTID, IMAGEID);

CREATE INDEX IDX_IROBJIMGMP_IMGID
ON IROBJECTIMAGEMAP (IMAGEID)
; 

-------------------------End Changes for IROBJECTIMAGEMAP----------------------------
go
-------------------------Remove Indexes and Primary key for ITSEARCHRESULTS------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'ITSEARCHRESULTS'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for ITSEARCHRESULTS------------

CREATE INDEX IDX_ITSRCHRSLTS_SRCHOBJ
ON ITSEARCHRESULTS (SEARCHQUERYID, OBJECTID)
; 
    
-------------------------End Changes for ITSEARCHRESULTS----------------------------
go
-------------------------Remove Indexes and Primary key for ITTERMOBJECTMAP------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'ITTERMOBJECTMAP'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 
-------------------------Add Indexes and Primary key for ITTERMOBJECTMAP------------



CREATE INDEX IDX_ITTRMOBJCTMAP_TRMID 
ON ITTERMOBJECTMAP (TERMID)
; 
-------------------------End Changes for ITTERMOBJECTMAP----------------------------
go
-------------------------Remove Indexes and Primary key for ITVALUES------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'ITVALUES'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 
-------------------------Add Indexes and Primary key for ITVALUES------------

CREATE INDEX IDX_ITVALUES_VALIDFLDID 
ON ITVALUES (VALUEID, FIELDID)
; 

-------------------------End Changes for ITVALUES----------------------------
go
-------------------------Remove Indexes and Primary key for ITVALUETOOBJECT------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'ITVALUETOOBJECT'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 
-------------------------Add Indexes and Primary key for ITVALUETOOBJECT------------
   
CREATE INDEX IDX_ITVALUETOOBJECT_VALID 
ON ITVALUETOOBJECT (VALUEID)
; 


-------------------------End Changes for ITVALUETOOBJECT----------------------------
go
-------------------------Remove Indexes and Primary key for DTTERMS------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTTERMS'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 
-------------------------Add Indexes and Primary key for DTTERMS------------
ALTER TABLE DTTERMS ADD CONSTRAINT PK_DTTERMS
    PRIMARY KEY (TERMID);
    
CREATE INDEX IDX_DTTERMS_KEYWD 
ON DTTERMS (KEYWORD)
;
-------------------------End Changes for DTTERMS---------------------------- 
go
-------------------------Remove Indexes and Primary key for DTVALUETOENTITYMAP------------


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'DTVALUETOENTITYMAP'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEFT(@description, 9) = 'clustered'
    BEGIN
         SET @name_clus = @name
         SET @description_clus = @description
         SET @keys_clus = @keys
    END --IF
    ELSE
    BEGIN
         --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
        --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
	IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
	END
	ELSE
	BEGIN
		EXEC('DROP INDEX ' + @tableName +'.' + @name)
	END
	--IF

	
	
    END --ELSE
    FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
    --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
    
    IF @description like '%primary key%'
	BEGIN
        	--PRINT 'Primary Key found'
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
	END
	ELSE
	BEGIN
	--IF
		EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
		--PRINT 'Primary Key NOT found'
		--PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        	EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
	END
	
END --IF 

-------------------------Add Indexes and Primary key for DTVALUETOENTITYMAP------------

ALTER TABLE DTVALUETOENTITYMAP ADD CONSTRAINT PK_DTVALUETOENTITY
    PRIMARY KEY (VALUEID, ENTITYTYPEID, ENTITYID, RELENTITYTYPEID, RELENTITYID, PARENTENTITYTYPEID, PARENTENTITYID);

CREATE INDEX IDX_DTVALUETOENTITYMAP_01
ON DTVALUETOENTITYMAP (EntityID, EntityTypeID)
; 

CREATE INDEX IDX_DTVALUETOENTITYMAP_02
ON DTVALUETOENTITYMAP (Preferred, EntityID)
; 

-------------------------End Changes for DTVALUETOENTITYMAP----------------------------
go


