Is it possible to compare schema between different banks in SQL Server?

Question

Is it possible to compare schema between different banks in SQL Server?

Navigation

#1 by (1 votes)

1

I have several different BDs, and I need to compare their Schemas, but doing this manually can go unnoticed. For context I will use database of lesser proportion than I work. Image here .

As you can see in the image, I have two databases, the hard_base and database_compare .

Problem: Both have the dbo.aluno table, but in database_name it has columns ID, Name, and RA . In the strong_base the columns are ID, Name, and Email , that is, the RA and Email field differs.

Another point that differs is that in the database_comparacao contains the dbo.disciplina table, which it does not have in the other database. Similarly in hard_base contains the dbo.currency table that does not contain the other.

Doubt: Is it possible to make this comparison between two different bases? If yes, how? I need something that returns the differences of the Schemas, NOT of the contained data.

In other forums I read about tablediff but did not answer me, or I misunderstood what he does.

I found some tools that do this ( dbForge Schema Compare and Open DBDiff ), I'd like to know if there is a tool inside the SQL Server itself that does this without having to download another application.

sql sql-server

asked by anonymous 07.12.2017 / 15:03

1 answer

Listen to an event in cookies Mask for fixed telephone field does not work

score 1 · Accepted Answer

Use this script and adapt to your need:

use master
go

DECLARE @Server1 VARCHAR(100) ='[LD38\SQLEXPRESS2005].'; --include a dot at the end
DECLARE @DB1 VARCHAR(100) = '[TestDB]';
DECLARE @Table1 VARCHAR(100) = 'Customer';

DECLARE @Server2 VARCHAR(100) ='[LD38\SQLEXPRESS2005].'; --include a dot at the end
DECLARE @DB2 VARCHAR(100) = '[TestDB2]';
DECLARE @Table2 VARCHAR(100) = 'Customer';

DECLARE @SQL NVARCHAR(MAX);


SET @SQL = 
'
SELECT Table1.ServerName,
       Table1.DBName,
       Table1.SchemaName,
       Table1.TableName,
       Table1.ColumnName,
       Table1.name DataType,
       Table1.Length,
       Table1.Precision,
       Table1.Scale,
       Table1.Is_Identity,
       Table1.Is_Nullable,
       Table2.ServerName,
       Table2.DBName,
       Table2.SchemaName,
       Table2.TableName,
       Table2.ColumnName,
       Table2.name DataType,
       Table2.Length,
       Table2.Precision,
       Table2.Scale,
       Table2.Is_Identity,
       Table2.Is_Nullable
FROM   
    (SELECT ''' + @Server1 + ''' ServerName, 
           ''' + @DB1 + ''' DbName,
           SCHEMA_NAME(t.schema_id) SchemaName,
           t.Name TableName,
           c.Name ColumnName,
           st.Name,
           c.Max_Length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @Server1 + @DB1 + '.sys.tables t
           INNER JOIN ' + @Server1 + @DB1 + '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN sys.types st ON St.system_type_id = c.System_Type_id AND st.user_type_id = c.user_type_id
    WHERE  t.Name = ''' + @Table1 + ''') Table1 
    FULL OUTER JOIN
    (SELECT ''' + @Server2 + ''' ServerName, 
           ''' + @DB2 + ''' DbName,
           SCHEMA_NAME(t.schema_id) SchemaName,
           t.name TableName,
           c.name ColumnName,
           st.Name,
           c.max_length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @Server2 + @DB2 + '.sys.tables t
           INNER JOIN ' + @Server2 + @DB2 + '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN sys.types st ON St.system_type_id = c.System_Type_id AND st.user_type_id = c.user_type_id
    WHERE  t.Name = ''' + @Table2 + ''') Table2
    ON Table1.ColumnName = Table2.ColumnName
ORDER BY CASE WHEN Table1.ColumnName IS NULL THEN 2 ELSE 1 END, Table1.ColumnName
'

EXEC sp_executesql @SQL

I created 2 Banks with tables with equal names and different columns: Result:

An idea: With the Script I would create a Stored Procedure, would take the tables as a parameter, make a list with the database tables and compare all the tables of your DB and make a nice summary. If you do, share with us! It will be very useful!