[SQL Server] Afficher le nombre de lignes dans les tables principales de la base SSISDB
DELETE FROM [dbo].[SSISDB_STAT] WHERE [date_collect]=DATEADD(DAY,-1,CAST(GETDATE() AS DATE))
INSERT INTO [dbo].[SSISDB_STAT]([type_collect],[nbre_lig],[date_collect])
SELECT [operation_id],COUNT(operation_id),DATEADD(DAY,-1,CAST(GETDATE() AS DATE)) FROM [SSISDB].[internal].[operation_messages]
WHERE CAST(message_time AS DATE)=DATEADD(DAY,-1,CAST(GETDATE() AS DATE))
GROUP BY [operation_id]
INSERT INTO [dbo].[SSISDB_STAT]([type_collect],[nbre_lig],[date_collect])
SELECT '[internal].[event_message_context]'AS [type_collect],COUNT(context_id),DATEADD(DAY,-1,CAST(GETDATE() AS DATE)) FROM [SSISDB].[internal].[event_message_context] T1
JOIN [SSISDB].[internal].[event_messages] T2 ON T2.operation_id=T1.operation_id AND T2.event_message_id=T1.event_message_id
JOIN [SSISDB].[internal].[operation_messages] T3 ON T3.operation_id=T2.operation_id AND T3.operation_message_id=T2.event_message_id
WHERE CAST(T3.message_time AS DATE)=DATEADD(DAY,-1,CAST(GETDATE() AS DATE))
INSERT INTO [dbo].[SSISDB_STAT]([type_collect],[nbre_lig],[date_collect])
SELECT'internal.event_messages' AS [type_collect],COUNT(T1.event_message_id),DATEADD(DAY,-1,CAST(GETDATE() AS DATE)) FROM [SSISDB].[internal].[event_messages] T1
JOIN [SSISDB].[internal].[operation_messages] T2 ON T2.operation_id=T1.operation_id AND T2.operation_message_id=T1.event_message_id
WHERE CAST(T2.message_time AS DATE)=DATEADD(DAY,-1,CAST(GETDATE() AS DATE))
INSERT INTO [dbo].[SSISDB_STAT]([type_collect],[nbre_lig],[date_collect])
SELECT'[internal].[operation_messages]' AS [type_collect],COUNT(T1.operation_message_id),DATEADD(DAY,-1,CAST(GETDATE() AS DATE)) FROM [SSISDB].[internal].[operation_messages] T1
WHERE CAST(T1.message_time AS DATE)=DATEADD(DAY,-1,CAST(GETDATE() AS DATE))