Select ISNULL(s.name, m.name) AS name

Into #Tables

From databasename1.sys.tables m

Full Join databasename2.sys.tables s On s.name = m.name

--Select * From #Tables Order By name

Declare @Columns1 Table (name Nvarchar(50))

Declare @Columns2 Table (name Nvarchar(50))

Declare @Columns Table (name Nvarchar(50))

Declare @Sonuc Table (Tur Nvarchar(50), Tablo Nvarchar(50), Kolon Nvarchar(50), Prosedur Nvarchar(50), Aciklama Nvarchar(MAX))

BEGIN -- Tablolar Karşılaştırıyor

Declare DonguTablo Cursor For Select * From #Tables Order By name

Declare @TblName Nvarchar(50)

OPEN DonguTablo

FETCH NEXT FROM DonguTablo INTO @TblName

WHILE @@FETCH_STATUS = 0

BEGIN

Declare @IsDb1Tbl bit = (Select COUNT(*) From databasename1.sys.tables where type='U' and name = @TblName)

Declare @IsDb1Tb2 bit = (Select COUNT(*) From databasename2.sys.tables where type='U' and name = @TblName)

IF @IsDb1Tbl = @IsDb1Tb2 -- Aynı tablolar var ise kolonlar karşılaştırılıyor

BEGIN

DELETE FROM @Columns

DELETE FROM @Columns1

DELETE FROM @Columns2

Insert Into @Columns1 (name)

Select c.name

From databasename1.sys.columns c

Left Join databasename1.sys.tables t On t.object_id = c.object_id

Where t.name = @TblName

Insert Into @Columns2 (name)

Select c.name

From databasename2.sys.columns c

Left Join databasename2.sys.tables t On t.object_id = c.object_id

Where t.name = @TblName

Insert Into @Columns (name)

Select ISNULL(s.name, m.name) AS name

From @Columns1 m

Full Join @Columns2 s On s.name = m.name

Declare DonguColumn Cursor For Select * From @Columns Order By name

Declare @ClmName Nvarchar(50)

OPEN DonguColumn

FETCH NEXT FROM DonguColumn INTO @ClmName

WHILE @@FETCH_STATUS = 0

BEGIN

Declare @IsDb1Cll bit = (Select COUNT(*) From @Columns1 where name = @ClmName)

Declare @IsDb1Cl2 bit = (Select COUNT(*) From @Columns2 where name = @ClmName)

IF @IsDb1Cll <> @IsDb1Cl2

BEGIN

IF @IsDb1Cll = 1

INSERT INTO @Sonuc (Tur, Tablo, Kolon, Aciklama)

Select 'Column', @TblName, @ClmName, '1. Veritabannda Eksik'

ELSE

INSERT INTO @Sonuc (Tur, Tablo, Kolon, Aciklama)

Select 'Column', @TblName, @ClmName, '2. Veritabannda Fazla'

END

FETCH NEXT FROM DonguColumn INTO @ClmName

END

CLOSE DonguColumn

DEALLOCATE DonguColumn

END

IF @IsDb1Tbl <> @IsDb1Tb2 -- Aynı tablolar yok ise farklı olan yazılıyor

BEGIN

IF @IsDb1Tbl = 1

INSERT INTO @Sonuc (Tur, Tablo, Aciklama)

Select 'Tablo', @TblName, '1. Veritabannda Eksik'

ELSE

INSERT INTO @Sonuc (Tur, Tablo, Aciklama)

Select 'Tablo', @TblName, '2. Veritabannda Fazla'

END

FETCH NEXT FROM DonguTablo INTO @TblName

END

CLOSE DonguTablo

DEALLOCATE DonguTablo

DROP TABLE #Tables

END

BEGIN -- Prosedürler Karşılaştırışıyor

Select p.name, REPLACE(REPLACE(REPLACE(REPLACE(definition, CHAR(13),' '), CHAR(10),' '), CHAR(9), ''), ' ', '') As definition

Into #Db1Proc

From databasename1.sys.procedures p

Left Join databasename1.sys.all_sql_modules m On m.object_id = p.object_id

Select p.name, REPLACE(REPLACE(REPLACE(REPLACE(definition, CHAR(13),' '), CHAR(10),' '), CHAR(9), ''), ' ', '') As definition

Into #Db2Proc

From databasename2.sys.procedures p

Left Join databasename2.sys.all_sql_modules m On m.object_id = p.object_id

Select ISNULL(s.name, m.name) AS name

Into #Procedures

From #Db1Proc m

Full Join #Db2Proc s On s.name = m.name

Declare DonguProc Cursor For Select * From #Procedures Order By name

Declare @ProcName Nvarchar(50)

OPEN DonguProc

FETCH NEXT FROM DonguProc INTO @ProcName

WHILE @@FETCH_STATUS = 0

BEGIN

Declare @IsDb1Prl bit = (Select COUNT(*) From #Db1Proc where name = @ProcName)

Declare @IsDb1Pr2 bit = (Select COUNT(*) From #Db2Proc where name = @ProcName)

IF @IsDb1Prl = @IsDb1Pr2 -- Aynı Prosedur var ise içerik karşılaştırılıyor

BEGIN

IF (Select REPLACE(definition, ' ', '') From #Db1Proc where name = @ProcName) <> (Select REPLACE(definition, ' ', '') From #Db2Proc where name = @ProcName)

INSERT INTO @Sonuc (Tur, Prosedur, Aciklama)

Select 'Prosedür', @ProcName, 'Farklılık var'

END

IF @IsDb1Prl <> @IsDb1Pr2 -- Aynı Prosedur yok ise yazılıyor

BEGIN

INSERT INTO @Sonuc (Tur, Prosedur, Aciklama)

Select 'Prosedür', @ProcName, 'Mevcut Değil var'

END

FETCH NEXT FROM DonguProc INTO @ProcName

END

CLOSE DonguProc

DEALLOCATE DonguProc

DROP TABLE #Procedures

DROP TABLE #Db1Proc

DROP TABLE #Db2Proc

END

Select * From @Sonuc Order By Tur