ZOUHAIER KHARROUBI

-- =============================================

-- Author:     Zouhaier KHARROUBI

-- Create date: 06/09/2022

-- Description: Comparer la taille des tables sur des bases sur deux instances SQL Server

-- =============================================

BEGIN

 

       SET NOCOUNT ON;

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'[tempdb]..[#BasesListe]'))

             BEGIN

                    DROP TABLE #BasesListe

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'[tempdb]..[#TablesListe]'))

             BEGIN

                    DROP TABLE #TablesListe

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'[tempdb]..[#TableTaille]'))

             BEGIN

                    DROP TABLE #TableTaille

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'[tempdb]..[#Serveurs_LiesTemp]'))

             BEGIN

                    DROP TABLE #Serveurs_LiesTemp

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'[tempdb]..[#Serveurs_Lies]'))

             BEGIN

                    DROP TABLE #Serveurs_Lies

             END

 

CREATE TABLE #BasesListe

(

Num_Base                         INT IDENTITY (1,1)

,ServerName                      VARCHAR(150)

,DataBaseName              NVARCHAR(128)

 

)

CREATE TABLE #TablesListe

(

Num_Tab                                 INT IDENTITY (1,1)

,ServerName                      VARCHAR(150)

,DataBaseName              NVARCHAR(128)

,TableName                       NVARCHAR(128)

,ShemaName                       NVARCHAR(128)

,[rows]                                 BIGINT

,[reserved]                      VARCHAR(64)

,[data]                                 VARCHAR(64)

,index_size                      VARCHAR(64)

,unusued                         VARCHAR(64)

)

 

CREATE TABLE #TableTaille

(

[name]                           SYSNAME

,[rows]                                 BIGINT

,reserved                        VARCHAR(64)

,[data]                                 VARCHAR(64)

,index_size                      VARCHAR(64)

,unusued                         VARCHAR(64)

)

 

 

CREATE TABLE #Serveurs_LiesTemp

(

SRV_NAME                         VARCHAR(150),

SRV_PROVIDERNAME           VARCHAR(150),

SRV_PRODUCT                      VARCHAR(150),

SRV_DATASOURCE                   VARCHAR(150),

SRV_PROVIDERSTRING         VARCHAR(150),

SRV_LOCATION               VARCHAR(150),

SRV_CAT                                 VARCHAR(150)

)                         

 

CREATE TABLE #Serveurs_Lies

(

Num_Lig                                 INT IDENTITY (1,1),

SRV_NAME                         VARCHAR(150),

SRV_DATASOURCE                   VARCHAR(150),

ServerName                       VARCHAR(150) NULL

) 

 

CREATE NONCLUSTERED INDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies

(

       ServerName ASC

)

 

 

 

 

DECLARE @Num_Serveur             AS INT

             ,@Nbre_Serveur                   AS INT

             ,@Num_Base                       AS INT

             ,@Nbre_Base                      AS INT

             ,@Num_Tab                        AS INT

             ,@Nbre_Tab                       AS INT

             ,@Serveur_Lie              AS VARCHAR(150)

             ,@Requete                        AS NVARCHAR(max)

             ,@LocalHot                       AS VARCHAR(50)

             ,@ServerName               AS VARCHAR(150)

             ,@DataBaseName                          AS NVARCHAR(128)

             ,@TableName                AS NVARCHAR(128)

             ,@ShemaName                AS NVARCHAR(128)

 

SELECT @LocalHot = QUOTENAME(@@SERVERNAME)

 

INSERT INTO #Serveurs_LiesTemp

EXEC sp_linkedservers

 

INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)

SELECT DISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp

WHERE SRV_NAME IN('MonServeur1\ServeurSQL1','MonServeur2\ServeurSQL2')

 

 

SELECT @Num_Serveur=1,@Nbre_Serveur=COUNT(Num_Lig) FROM #Serveurs_Lies

 

WHILE  @Num_Serveur <= @Nbre_Serveur

       BEGIN

             BEGIN TRY

                    SELECT @Serveur_Lie =SRV_DATASOURCE FROM #Serveurs_Lies WHERE Num_Lig=@Num_Serveur

 

                    SELECT @Requete     =     'SELECT ''' + @Serveur_Lie + ''' AS ServerName,name FROM ' + QUOTENAME(@Serveur_Lie) + '.master.sys.databases WHERE name LIKE ''Mektaba%'' OR name LIKE ''Formation%'''

                   

                    INSERT INTO  #BasesListe(ServerName     ,DataBaseName)

                    EXECUTE sp_executesql @Requete   

 

                    SELECT @Num_Base=1,@Nbre_Base=COUNT(DataBaseName) FROM #BasesListe

 

                    WHILE @Num_Base<=@Nbre_Base

                          BEGIN

                                 SELECT @DataBaseName=DataBaseName FROM #BasesListe WHERE Num_Base=@Num_Base

 

                                 SELECT @Requete='SELECT ''' + @Serveur_Lie + ''',''' + @DataBaseName +''', TABLE_NAME COLLATE French_BIN, TABLE_SCHEMA COLLATE French_BIN FROM '

 

                   + QUOTENAME(@Serveur_Lie) + '.' + @DataBaseName + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''

 

                                 INSERT INTO #TablesListe(ServerName,DataBaseName, TableName, ShemaName)

                                 EXECUTE sp_executesql @Requete

 

                                 SELECT @Nbre_tab=COUNT(DataBaseName),@Num_Tab=1 FROM #TablesListe

 

                                 WHILE @Num_Tab<=@Nbre_tab

                                       BEGIN

      

                                          SELECT @ServerName=ServerName,@DataBaseName=DataBaseName, @TableName=TableName, @ShemaName=ShemaName

                                          FROM #TablesListe WHERE Num_Tab=@Num_Tab

                                         

                                          --SELECT @Requete = 'EXEC ' + QUOTENAME(@ServerName) + '.' + QUOTENAME(@DataBaseName) + '..sp_spaceused ''' + @ShemaName +'.' + @TableName +'''';

                                          --INSERT INTO #TableTaille

                                          --EXECUTE sp_executesql @Requete

                                         

                                          SELECT @Requete = 'TRUNCATE TABLE [master].dbo.[TableTaille]; INSERT INTO [master].dbo.[TableTaille] EXEC ' + QUOTENAME(@DataBaseName) + '..sp_spaceused ''''' + @ShemaName +'.' + @TableName +'''''';

                                          SELECT @Requete = QUOTENAME(@ServerName) + '.[master].[dbo].[usp_exec_requete]' + ' ''' + @Requete + ''''

                                          EXECUTE sp_executesql @Requete

 

                                          SELECT @Requete = 'SELECT * FROM ' + QUOTENAME(@ServerName) + '.[master].dbo.[TableTaille]'

                                          INSERT INTO #TableTaille

                                          EXECUTE sp_executesql @Requete

 

                                         

                                          UPDATE #TablesListe

                                          SET   [rows]       = T1.[rows],

                                                     reserved   = T1.reserved,

                                                     [data]       = T1.[data],

                                                     index_size = T1.index_size,

                                                     unusued   = T1.unusued

                                          FROM   #TableTaille AS T1

                                          WHERE DataBaseName = @DataBaseName

                                               AND ShemaName = @ShemaName

                                               AND TableName = @TableName

                                               AND ServerName=@Serveur_Lie

 

                                          TRUNCATE TABLE #TableTaille

                                        

                                          SELECT @Num_Tab = @Num_Tab + 1

 

                                       END

 

                                 SELECT @Num_Base= @Num_Base +  @Num_Base + 1

                          END

            

             END TRY

 

             BEGIN CATCH

                    SELECT @Serveur_Lie AS Serveur_Lie,@Requete AS Requete, ERROR_MESSAGE() AS Message_Erreur

             END CATCH

 

             SELECT  @Num_Serveur =   @Num_Serveur + 1

       END

 

IF EXISTS(SELECT * FROM [master]..sysobjects WHERE id = object_id(N'[master].dbo.TablesListe'))

       BEGIN

             DROP TABLE [master].dbo.TablesListe

       END

 

SELECT ServerName,DataBaseName, ShemaName,TableName,

       [rows], reserved, [data], index_size, unusued

          INTO [master].dbo.TablesListe

FROM   #TablesListe

ORDER BY ServerName,DataBaseName, ShemaName,TableName

 

DROP TABLE #BasesListe

DROP TABLE #TablesListe

DROP TABLE #TableTaille

DROP TABLE #Serveurs_LiesTemp

DROP TABLE #Serveurs_Lies

 

SELECT T1.T1_rows-T2.T2_rows AS EcartLigne

,T1.T1_ServerName,T1.T1_DataBaseName,T1.T1_ShemaName,T1.T1_TableName,T1.T1_rows,T2.T2_ServerName,T2.T2_rows,T1.T1_rows-T2.T2_rows AS EcartLigne

FROM (

SELECT  [ServerName] AS T1_ServerName

      ,[DataBaseName] AS T1_DataBaseName

      ,[ShemaName] AS T1_ShemaName

      ,[TableName] AS T1_TableName

      ,[rows] AS T1_rows

      ,[reserved] AS T1_reserved

      ,[data] AS T1_data

      ,[index_size] AS T1_index_size

      ,[unusued] AS T1_unusued

FROM [master].[dbo].[TablesListe]

WHERE ServerName='MonServeur1\ServeurSQL1'

) T1

JOIN (

SELECT  [ServerName] AS T2_ServerName

      ,[DataBaseName] AS T2_DataBaseName

      ,[ShemaName] AS T2_ShemaName

      ,[TableName] AS T2_TableName

      ,[rows] AS T2_rows

      ,[reserved] AS T2_reserved

      ,[data] AS T2_data

      ,[index_size] AS T2_index_size

      ,[unusued] AS T2_unusued

FROM [master].[dbo].[TablesListe]

WHERE ServerName='MonServeur2\ServeurSQL2'

) T2 ON T2.T2_DataBaseName=T1.T1_DataBaseName AND T2.T2_ShemaName=T1.T1_ShemaName AND T2.T2_TableName=T1.T1_TableName

WHERE T1.T1_rows-T2.T2_rows<>0

ORDER BY T1.T1_ServerName,T1.T1_DataBaseName,T1.T1_ShemaName,T1.T1_TableName

 

 

END

GO