Przedstawiam rozwinięcie skryptu "SQL Server - Jak stworzyć listę tabel z ilościami wierszy w bazie?" przydatnego przy porównywaniu nazw tabel i ilości rekordów w bazach danych umieszczonych na różnych serwerach. Skrypt uruchomiamy jest na serwerze źródłowym, a wygenerowany automatycznie na serwerze docelowym.

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

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