
/*Creación de índices*/

/*Indice doc_documento.id_documento*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[doc_documento]') AND name = N'IX_id_documento')

	CREATE NONCLUSTERED INDEX IX_id_documento ON [dbo].[doc_documento] 
	(
		[id_documento] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX [IX_id_documento] ON [dbo].[doc_documento] 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX [IX_id_documento] ON [dbo].[doc_documento] 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

/*Indice doc_documento.id_tercerocliente*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[doc_documento]') AND name = N'IX_id_tercero_cliente')

	CREATE NONCLUSTERED INDEX IX_id_tercero_cliente ON [dbo].[doc_documento] 
	(
		[id_tercero_cliente] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_tercero_cliente ON [dbo].[doc_documento] 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_tercero_cliente ON [dbo].[doc_documento] 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

/*Indice doc_documento.id_terceroproveedor*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[doc_documento]') AND name = N'IX_id_tercero_proveedor')

	CREATE NONCLUSTERED INDEX IX_id_tercero_proveedor ON [dbo].[doc_documento] 
	(
		[id_tercero_proveedor] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_tercero_proveedor ON [dbo].[doc_documento] 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_tercero_proveedor ON [dbo].[doc_documento] 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

/*Indice doc_itemdocumento.id_documento*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[doc_itemdocumento]') AND name = N'IX_id_documento')

	CREATE NONCLUSTERED INDEX IX_id_documento ON [dbo].doc_itemdocumento 
	(
		[id_documento] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_documento ON [dbo].doc_itemdocumento 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_documento ON [dbo].doc_itemdocumento 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

/*Indice doc_itemdocumento.id_itemDocumento*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[doc_itemdocumento]') AND name = N'IX_id_itemDocumento')

	CREATE NONCLUSTERED INDEX IX_id_itemDocumento ON [dbo].doc_itemdocumento 
	(
		[id_itemDocumento] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_itemDocumento ON [dbo].doc_itemdocumento 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_itemDocumento ON [dbo].doc_itemdocumento 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

/*Indice ort_programacion.id_reportetecnico*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[ort_programacion]') AND name = N'IX_id_reporteTecnico')

	CREATE NONCLUSTERED INDEX IX_id_reporteTecnico ON [dbo].ort_programacion 
	(
		id_reporteTecnico ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_reporteTecnico ON [dbo].ort_programacion 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_reporteTecnico ON [dbo].ort_programacion 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO
/*Indice ort_programacion.id_usuario*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[ort_programacion]') AND name = N'IX_id_usuario')

	CREATE NONCLUSTERED INDEX IX_id_usuario ON [dbo].ort_programacion 
	(
		id_usuario ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_usuario ON [dbo].ort_programacion 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_usuario ON [dbo].ort_programacion 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO
/*Indice ort_programacion.[id_documento.ot]*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[ort_programacion]') AND name = N'IX_id_documento_ot')

	CREATE NONCLUSTERED INDEX IX_id_documento_ot ON [dbo].ort_programacion 
	(
		[id_documento.ot] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_documento_ot ON [dbo].ort_programacion 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_documento_ot ON [dbo].ort_programacion 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

/*Indice doc_documentoAtributo.[id_documento]*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[doc_documentoAtributo]') AND name = N'IX_id_documento')

	CREATE NONCLUSTERED INDEX IX_id_documento ON [dbo].doc_documentoAtributo 
	(
		[id_documento] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_documento ON [dbo].doc_documentoAtributo 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_documento ON [dbo].doc_documentoAtributo 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

/*Indice cnt_visitaFija.[id_contratoEquipo]*/
IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[cnt_visitaFija]') AND name = N'IX_id_contratoEquipo')

	CREATE NONCLUSTERED INDEX IX_id_contratoEquipo ON [dbo].cnt_visitaFija 
	(
		[id_contratoEquipo] ASC
		
	)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER INDEX IX_id_contratoEquipo ON [dbo].cnt_visitaFija 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_id_contratoEquipo ON [dbo].cnt_visitaFija 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

IF NOT EXISTS (SELECT * 
			FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[dbo].[doc_documento.ot]') AND name = N'IX_fechaModificacion')

CREATE NONCLUSTERED INDEX IX_fechaModificacion ON dbo.[doc_documento.ot]
	(
	fechaModificacion DESC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.[doc_documento.ot] SET (LOCK_ESCALATION = TABLE)
GO
ALTER INDEX IX_fechaModificacion ON [dbo].[doc_documento.ot] 
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

ALTER INDEX IX_fechaModificacion ON [dbo].[doc_documento.ot] 
REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

exec sp_updatestats
GO

/*Compresión del LOG*/

SELECT file_id, name  
FROM sys.database_files;  
GO  
DBCC SHRINKFILE (2, TRUNCATEONLY)
GO

/* Reparación de Base de datos */

declare @nameBD varchar(100)
declare @query varchar(500)
SELECT @nameBD = DB_NAME()

set @query='
	ALTER DATABASE ' + @nameBD + '  SET EMERGENCY;
	ALTER DATABASE ' + @nameBD + ' SET SINGLE_USER;
	DBCC CHECKDB (' + @nameBD + ', ''REPAIR_ALLOW_DATA_LOSS'');
	ALTER DATABASE ' + @nameBD + ' SET MULTI_USER;
'

exec (@query)
go
