Is there a possibility to compare schema between different databases in SQL Server?

Question

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

As you can see from the image, I have two databases, the base_forte and the base_comparacao.

Problem: Both have the table dbo student., however, in base_comparacao it has columns ID, Name and RA. Already in the base_forte the columns are ID, Name and Email, that is, the field differs RA and Email.

Another point that differ is that in base_comparacao contains the table dbo discipline., what is not in the other bank. Similarly in the base_forte contains the table dbo. that do not count in the other.

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

In other forums I read about the tablediff but he didn’t answer me, or I misunderstood what he does.

UPDATE: I found some tools that do this (dbForge Schema Compare and Open Dbdiff), I wonder if there is a tool within SQL Server itself that does this without needing to download another application.

Answer 1

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: Upshot:

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