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
.
