-- 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.--

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 


DELETE FROM "SLSTANDARDSSTARTUP" WHERE 
  (StandardName = 'CDWA' or StandardName = 'ObjectID' or StandardName = 'DublinCore' or 
   StandardName = 'USMARC' or StandardName = 'Visual Resource Association v3.0' or StandardName = 'CIMI')
go

INSERT INTO "SLStandardsStartup" ("StandardID","StandardName","CreateThumbCache","Thumbnail1FieldID","Thumbnail2FieldID","Thumbnail3FieldID","Thumbnail4FieldID","Sort1FieldID","Sort2FieldID","Sort3FieldID","Sort4FieldID") VALUES ( 0, 'CDWA', 0, 101, 17, 119, 3, 17, 101, 119, 3 )
go
INSERT INTO "SLStandardsStartup" ("StandardID","StandardName","CreateThumbCache","Thumbnail1FieldID","Thumbnail2FieldID","Thumbnail3FieldID","Thumbnail4FieldID","Sort1FieldID","Sort2FieldID","Sort3FieldID","Sort4FieldID") VALUES ( 1, 'ObjectID', 1, 9, 6, 8, 1, 6, 9, 8, 1 )
go
INSERT INTO "SLStandardsStartup" ("StandardID","StandardName","CreateThumbCache","Thumbnail1FieldID","Thumbnail2FieldID","Thumbnail3FieldID","Thumbnail4FieldID","Sort1FieldID","Sort2FieldID","Sort3FieldID","Sort4FieldID") VALUES ( 2, 'DublinCore', 1, 6, 3, 8, 1, 3, 6, 8, 1 )
go
INSERT INTO "SLStandardsStartup" ("StandardID","StandardName","CreateThumbCache","Thumbnail1FieldID","Thumbnail2FieldID","Thumbnail3FieldID","Thumbnail4FieldID","Sort1FieldID","Sort2FieldID","Sort3FieldID","Sort4FieldID") VALUES ( 3, 'USMARC', 1, 10, 3, 16, 1, 3, 10, 16, 1 )
go
INSERT INTO "SLStandardsStartup" ("StandardID","StandardName","CreateThumbCache","Thumbnail1FieldID","Thumbnail2FieldID","Thumbnail3FieldID","Thumbnail4FieldID","Sort1FieldID","Sort2FieldID","Sort3FieldID","Sort4FieldID") VALUES ( 4, 'Visual Resource Association v3.0', 1, 19, 3, 21, 2, 3, 19, 21, 2 )
go
INSERT INTO "SLStandardsStartup" ("StandardID","StandardName","CreateThumbCache","Thumbnail1FieldID","Thumbnail2FieldID","Thumbnail3FieldID","Thumbnail4FieldID","Sort1FieldID","Sort2FieldID","Sort3FieldID","Sort4FieldID") VALUES ( 5, 'CIMI', 1, 79, 64, 75, 63, 64, 79, 75, 63 )
go



-------------------------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 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


