Samuele E. Locatelli
328 lines
9.0 KiB
Transact-SQL
328 lines
9.0 KiB
Transact-SQL
SET xact_abort ON
|
|
GO
|
|
|
|
BEGIN TRANSACTION
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: Marco Salvi
|
|
-- Create date: 11/03/2009
|
|
-- Description: Stored procedure per ricostruire gli indici e fare lo shrink su un db
|
|
-- =============================================
|
|
CREATE PROCEDURE sp_IndiciAndShrink
|
|
(
|
|
@databaseName sysname
|
|
)
|
|
AS
|
|
BEGIN
|
|
|
|
-- elimino eventuale tab temporanea preesistente
|
|
IF (SELECT COUNT(*) FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMP_nomiTabelle') > 0
|
|
BEGIN
|
|
DROP TABLE ##TMP_nomiTabelle
|
|
END
|
|
|
|
-- enumero le tab del db in una tab temporanea
|
|
SELECT IDENT = IDENTITY (INT,1,1), NAME
|
|
INTO ##TMP_nomiTabelle
|
|
FROM sys.Tables
|
|
|
|
-- eseguo reindicizzazione
|
|
DECLARE @VAR1 INT
|
|
SET @VAR1 = 1
|
|
WHILE @VAR1 < = ( SELECT COUNT(*) FROM ##TMP_nomiTabelle)
|
|
BEGIN
|
|
|
|
DECLARE @OBJECTNAME VARCHAR(256)
|
|
|
|
-- carico il nome della tab i-esima
|
|
SELECT @OBJECTNAME = NAME
|
|
FROM ##TMP_nomiTabelle
|
|
WHERE [IDENT] = @VAR1
|
|
|
|
-- rebuild indice
|
|
DBCC DBREINDEX(@OBJECTNAME)
|
|
|
|
-- incremento
|
|
SET @VAR1 = @VAR1 + 1
|
|
|
|
END
|
|
|
|
--shrink del db
|
|
DBCC SHRINKDATABASE (@databaseName)
|
|
|
|
END
|
|
GO
|
|
|
|
COMMIT
|
|
GO
|
|
|
|
|
|
SET xact_abort ON
|
|
GO
|
|
|
|
BEGIN TRANSACTION
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
ALTER VIEW v_elencoIntervFilt
|
|
AS
|
|
SELECT TOP (100) PERCENT dbo.InterventiMtz.numIntMtz, dbo.InterventiMtz.richiesta, dbo.AnagImpianti.codImpianto, dbo.AnagImpianti.nomeImpianto,
|
|
dbo.AnagMacchine.codMacchina, dbo.AnagMacchine.nomeMacchina, dbo.InterventiMtz.descrizione, dbo.InterventiMtz.inizioIntervento,
|
|
dbo.InterventiMtz.fineIntervento, ISNULL(dbo.InterventiMtz.descrizioneIntervento, 'n.d. (report mtz)') AS descrizioneIntervento, ISNULL(dbo.AnagCausali.descrCausale,
|
|
'n.d. (causale)') AS descrCausale, ROUND(CAST(ISNULL(dbo.v_interventiErogati.totMinErogati, 0) AS FLOAT) / 60, 2) AS minErogati,
|
|
ROUND(CAST(ISNULL(DATEDIFF(minute, dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento), 0) AS FLOAT) / 60, 2) AS durataMtz, CASE (isFermo)
|
|
WHEN 1 THEN ROUND(CAST(ISNULL(DATEDIFF(minute, dbo.InterventiMtz.guasto, dbo.InterventiMtz.fineIntervento), 0) AS FLOAT) / 60, 2)
|
|
WHEN 0 THEN ROUND(CAST(ISNULL(DATEDIFF(minute, dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento), 0) AS FLOAT) / 60, 2) END AS durataOff,
|
|
dbo.InterventiMtz.guasto, dbo.InterventiMtz.idxStato, dbo.InterventiMtz.idxImpianto, dbo.InterventiMtz.idxMacchina
|
|
FROM dbo.InterventiMtz INNER JOIN
|
|
dbo.AnagAmbitoGuasto ON dbo.InterventiMtz.idxAmbito = dbo.AnagAmbitoGuasto.idxAmbito INNER JOIN
|
|
dbo.AnagImpianti ON dbo.InterventiMtz.idxImpianto = dbo.AnagImpianti.idxImpianto INNER JOIN
|
|
dbo.AnagMacchine ON dbo.InterventiMtz.idxMacchina = dbo.AnagMacchine.idxMacchina INNER JOIN
|
|
dbo.AnagTipoGuasto ON dbo.InterventiMtz.idxTipo = dbo.AnagTipoGuasto.idxTipo INNER JOIN
|
|
dbo.AnagPriorita ON dbo.InterventiMtz.idxPriorita = dbo.AnagPriorita.idxPriorita INNER JOIN
|
|
dbo.AnagStati ON dbo.InterventiMtz.idxStato = dbo.AnagStati.idxStato LEFT OUTER JOIN
|
|
dbo.v_interventiErogati ON dbo.InterventiMtz.numIntMtz = dbo.v_interventiErogati.numIntMtz LEFT OUTER JOIN
|
|
dbo.AnagCausali ON dbo.InterventiMtz.idxCausale = dbo.AnagCausali.idxCausale
|
|
ORDER BY dbo.InterventiMtz.numIntMtz DESC
|
|
GO
|
|
|
|
EXEC sp_updateextendedproperty 'MS_DiagramPane1', '[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
Begin Table = "InterventiMtz"
|
|
Begin Extent =
|
|
Top = 8
|
|
Left = 486
|
|
Bottom = 354
|
|
Right = 671
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagAmbitoGuasto"
|
|
Begin Extent =
|
|
Top = 11
|
|
Left = 82
|
|
Bottom = 89
|
|
Right = 255
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagImpianti"
|
|
Begin Extent =
|
|
Top = 123
|
|
Left = 219
|
|
Bottom = 231
|
|
Right = 370
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagMacchine"
|
|
Begin Extent =
|
|
Top = 190
|
|
Left = 826
|
|
Bottom = 298
|
|
Right = 977
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagTipoGuasto"
|
|
Begin Extent =
|
|
Top = 90
|
|
Left = 1143
|
|
Bottom = 168
|
|
Right = 1294
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagPriorita"
|
|
Begin Extent =
|
|
Top = 23
|
|
Left = 958
|
|
Bottom = 116
|
|
Right = 1109
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagStati"
|
|
Begin Extent =
|
|
Top = 263
|
|
Left = 90
|
|
Bottom = 356
|
|
Right = 241
|
|
', 'SCHEMA', 'dbo', 'VIEW', 'v_elencoIntervFilt'
|
|
GO
|
|
|
|
EXEC sp_updateextendedproperty 'MS_DiagramPane2', ' End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "v_interventiErogati"
|
|
Begin Extent =
|
|
Top = 3
|
|
Left = 739
|
|
Bottom = 81
|
|
Right = 890
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagCausali"
|
|
Begin Extent =
|
|
Top = 291
|
|
Left = 1011
|
|
Bottom = 369
|
|
Right = 1162
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 30
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1665
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1575
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 1440
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
', 'SCHEMA', 'dbo', 'VIEW', 'v_elencoIntervFilt'
|
|
GO
|
|
|
|
COMMIT
|
|
GO
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(5, GETDATE())
|
|
GO
|