[SQL Server] Comparer la taille des tables sur des bases sur deux instances SQL Server
--
=============================================
--
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