Porównanie nazwy tabel i ilości rekordów bazy na dwóch instancjach MS SQL Servera.

Przedstawiam rozwinięcie skryptu „Informacje o tabelach, ilościach wierszy i wielkościach danych zawartych w bazie danych.” przydatnego przy porównywaniu nazw tabel i ilości rekordów w bazach danych umieszczonych na różnych serwerach. Niżej przedstawiony skrypt uruchamiamy na serwerze źródłowym, wynikiem którego jest wygenerowany automatycznie skrypt służący do uruchomienia na serwerze docelowym.

Wynikiem tego skryptu będzie tabela przedstawiająca różnice w występowaniu tabel czy też różnej ilości rekordów w bazie danych.

Testowany na SQL Server 2000, 2008.

/* Author Dariusz 'DBSoft' Brejnak (c) 2010
name        rows Database
publishers1    8 Source -- inna nazwa tabeli lub brak w destination
titles        28 Source   -- inna ilość rekordów
publishers     8 Destination -- inna nazwa tabeli lub brak w source
sales         21 Destination -- brak tabeli w source
titles        18 Destination -- inna ilość rekordów
*/

SET NOCOUNT ON
CREATE TABLE #tmp
([name] varchar(50),
[rows] int,
[reserved] varchar (20),
[data] varchar (20),
[index_size] varchar(20),
[unsed] varchar(20)
)
CREATE TABLE #tmprows_source
([servername] varchar(50),
[basename] varchar(50),
[name] varchar(50),
[rows] int,
[reserved] varchar (20),
[data] varchar (20),
[index_size] varchar(20),
[unsed] varchar(20)
)
INSERT #tmp EXEC sp_MSforeachtable 'exec sp_spaceused ''?'''

INSERT #tmprows_source
SELECT @@SERVERNAME,
DB_NAME(),
[name],
[rows],
replace(reserved,'KB',''),
replace(data,'KB',''),
replace(index_size,'KB',''),
replace(unsed,'KB','')
FROM #tmp

SELECT [servername],
[basename],
[name],
[rows],
[reserved],
[data],
[index_size],
[unsed]
FROM #tmprows_source
ORDER BY [name]

PRINT '/*
Script : CompareTablesInDatabase.sql
Author : Dariusz ''DBSoft'' Brejnak (c) 2010
generated automatically on  '+@@SERVERNAME+'*/'
PRINT
'CREATE TABLE #tmprows_source
([servername] varchar(50),
[basename] varchar(50),
[name] varchar(50),
[rows] int,
[reserved] varchar (20),
[data] varchar (20),
[index_size]varchar(20),
[unsed] varchar(20)
)'
PRINT ''
DECLARE @servername varchar(50),
@basename varchar(50),
@name varchar(50),
@rows int,
@reserved int,
@data int,
@index_size int,
@unused int

DECLARE DB_Cursor CURSOR FOR
SELECT [servername],
[basename],
[name],
[rows],
[reserved],
[data],
[index_size],
[unsed]
FROM #tmprows_source

OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor
INTO @servername,@basename,@name,@rows,@reserved,

@data,@index_size,@unused

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'INSERT INTO #tmprows_source VALUES ('''+@servername+''','+
''''+@basename+''','+
''''+@name+''','+
cast(@rows as varchar(10))+','+
cast(@reserved as varchar(10))+','+
cast(@data as varchar(10))+','+
cast(@index_size as varchar(10))+','+
cast(@unused as varchar(10))+
+')'
FETCH NEXT FROM DB_Cursor
INTO @servername,@basename,@name,@rows,@reserved,

@data,@index_size,@unused
END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

PRINT ''
PRINT 'CREATE TABLE #tmp
([name] varchar(50),
[rows] int,
[reserved] varchar (20),
[data] varchar (20),
[index_size]varchar(20),
[unsed] varchar(20)
)
CREATE TABLE #tmprows_destination
([servername] varchar(50),
[basename] varchar(50),
[name] varchar(50),
[rows] int,
[reserved] varchar (20),
[data] varchar (20),
[index_size]varchar(20),
[unsed] varchar(20)
)'

PRINT 'INSERT #tmp EXEC sp_MSforeachtable ''exec sp_spaceused ''''?'''''''

PRINT 'INSERT #tmprows_destination
SELECT @@SERVERNAME,
DB_NAME(),
[name],
[rows],
replace(reserved,''KB'',''''),
replace(data,''KB'',''''),
replace(index_size,''KB'',''''),
replace(unsed,''KB'','''')
FROM #tmp'

PRINT '
SELECT [name], [rows],''Source'' as ''Database''
FROM  #tmprows_source
EXCEPT
SELECT [name], [rows],''Source'' as ''Database''
FROM #tmprows_destination
UNION ALL
SELECT [name], [rows],''Destination'' as ''Database''
FROM #tmprows_destination
EXCEPT
SELECT [name], [rows],''Destination'' as ''Database''
FROM #tmprows_source

DROP TABLE #tmp
DROP TABLE #tmprows_source
DROP TABLE #tmprows_destination'
DROP TABLE #tmp
DROP TABLE #tmprows_source

.

DBBS